Scribd
Upload
9 views10 pages

SQL Queries for Database Management

The document is a submission for a Database Management System (DBMS) course by a BCA student, containing SQL queries related to table operations. It includes queries for creating, modifying, and deleting tables, as well as displaying and updating data within those tables. The document serves as a practical guide for performing various SQL operations in a structured format.

Uploaded by

moonknightpluto
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views10 pages

SQL Queries for Database Management

The document is a submission for a Database Management System (DBMS) course by a BCA student, containing SQL queries related to table operations. It includes queries for creating, modifying, and deleting tables, as well as displaying and updating data within those tables. The document serves as a practical guide for performing various SQL operations in a structured format.

Uploaded by

moonknightpluto
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

DBMS

BCA
3RD SEMESTER

SUBMITTED BY: Tejus Tagra


ENROLLMENT NO: A25304824020

SUBMITTED TO: DR. PARAMPREET KAUR


INDEX
[Link] TITLE REMARKS
1 Write a Query to create a table in SQL.

2 Write a Query to display structure of table


in SQL.
3 Write a Query to insert a row in table.

4 Write a Query to display roll number and


name from table.
5 Write a Query to add gender in table
student.
6 Write a Query to modify data type in table.

7 Write a Query to update branch of student


in table.
8 Write a Query to delete entry in a table.

9 Write a Query to rename column in table.

10 Write a Query to rename table in SQL.

11 Write a Query to use Drop Statement.

12 Write a Query to sort entries in a table


using Order By clause.
13 Write a Query to use aggregate functions in
SQL.
[Link] TITLE REMARKS
14 Use of substring comparison.

15 Use of order by statement.

16 Write a Query to insert a row in table.

17 Write a Query to display roll number and


name from table.
18 Write a Query to add gender in table
student.
19 Write a Query to modify data type in table.

20 Write a Query to update branch of student


in table.
21 Write a Query to delete entry in a table.

22 Write a Query to rename column in table.

23 Write a Query to rename table in SQL.

24 Write a Query to use Drop Statement.

25 Write a Query to sort entries in a table


using Order By clause.
26 Write a Query to use aggregate functions in
SQL.
Q1. Write a Query to create a table in SQL.
CODE:

CREATE TABLE student


(
RollNo INT PRIMARY KEY,
Name VARCHAR(20),
Branch VARCHAR(10)
);

OUTPUT:

Q2. Write a Query to display structure of table in SQL.


CODE:

DESC student;
OUTPUT:

Q3. Write a Query to insert a row in table.


CODE:
INSERT INTO student (RollNo, Name, Branch)
VALUES (1, 'Samridh', 'BCA');

OUPUT:

Q4. Write a Query to display roll number and name from table.
CODE:
SELECT ROLLNO, NAME FROM student;

OUTPUT:

Q5. Write a Query to add gender in table student.


CODE:
ALTER TABLE STUDENT ADD GENDER VARCHAR(10);
OUTPUT:

Q6. Write a Query to modify data type in table.


CODE:
ALTER TABLE student MODIFY NAME VARCHAR(30);
ALTER TABLE student MODIFY ROLLNO VARCHAR(5);

OUTPUT:

Q7. Write a Query to update branch of student in table.


CODE:
UPDATE student SET BRANCH='BTECH' WHERE
ROLLNO=1;

OUTPUT:

Q8. Write a Query to delete entry in a table.


CODE:
DELETE FROM student WHERE ROLLNO=1;
OUTPUT:

Q9. Write a Query to rename column in table.


CODE:
ALTER TABLE student RENAME COLUMN NAME TO
STUDENT_NAME;

OUTPUT:

Q10. Write a Query to rename table in SQL.


CODE:
ALTER TABLE student RENAME TO STUDENT_DETAILS;

OUTPUT:

Q11. Write a Query to use Drop Statement.


CODE:
DROP TABLE STUDENT_DETAILS;

OUTPUT:
Q12. Write a Query to sort entries in a table using Order By
clause.
CODE:
SELECT * FROM STUDENT ORDER BY ROLLNO DESC;

OUTPUT:
Q13. Write a Query to use aggregate functions in SQL.
CODE:
COUNT: SELECT COUNT(*) AS ROLLNO FROM STUDENT;

SUM: SELECT SUM(MARKS) AS MARKS FROM STUDENT;

AVG: SELECT AVG(MARKS) AS MARKS FROM STUDENT;

MIN: SELECT MIN(MARKS) AS MARKS FROM STUDENT;

MAX: SELECT MAX(MARKS) AS MARKS FROM STUDENT;

Common questions

Powered by AI

Modifying the datatype of a column can significantly impact the existing data quality and accessibility. For example, changing a VARCHAR to INT may result in data loss or conversion errors if the existing data cannot be directly cast to the new datatype. Precautions include backing up data, validating the compatibility of existing data with the new datatype, and potentially altering data values to ensure smooth conversion. Additionally, understanding how this change might affect indexes or constraints is crucial to maintain overall database performance and integrity .

The 'DROP TABLE' command in SQL is used to remove an entire table from the database schema, which includes deleting the table's structure and all its data. This action is irreversible and results in the permanent loss of all data stored within the table, impacting any relationships or constraints linked to it. It demands careful evaluation before execution to prevent accidental data loss and should be securely backed up or evaluated for dependencies within the database .

Using the 'RENAME' statement on a table alters its name, which can have consequences such as breaking existing queries, scripts, or applications if they reference the table by its old name. Effective management involves updating all dependencies, such as stored procedures, views, and application code, and adopting a systematic approach to change management, including comprehensive testing and documentation. It is essential to communicate these changes to all stakeholders to prevent operational disruptions .

Aggregate functions in SQL, such as COUNT, SUM, AVG, MIN, and MAX, are used to perform calculations on sets of values to return a single scalar value. These functions enhance data analysis by allowing users to summarize and make sense of large datasets quickly. For example, COUNT() can be used to determine the number of entries in a table, SUM() to get the total sum of numerical values in a column, and AVG() to find the average value, which aids in statistical analysis and reporting .

Updating a specific row in a table involves identifying the row by a unique identifier, such as the primary key, and using the SQL 'UPDATE' command to change the desired values. The steps include specifying the table, setting the new value with the 'SET' clause, and using a 'WHERE' clause to filter for the specific row. Precision is crucial in maintaining database integrity, as an incorrect update might lead to data inconsistency, loss of data integrity, or accidental updates of multiple rows if conditions are not well defined, which could impact downstream data operations and analyses .

Adding new columns to an existing table using 'ALTER TABLE' enhances database scalability and flexibility by allowing schema updates without the need for restructuring the entire database. This capability supports evolving data needs and application requirements by enabling incremental schema design, thus facilitating the addition of new features or accommodating new types of data efficiently. It helps in future-proofing the database against changing requirements .

The 'ALTER TABLE' SQL statement is used for both modifying a column's datatype and renaming a column, but the syntax differs. When modifying a column's datatype, the syntax is 'ALTER TABLE table_name MODIFY column_name new_datatype;'. For renaming a column, the syntax is 'ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;' .

To change a column name in a SQL table, the query 'ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;' is used. Such a change might be necessary for clarity, aligning with naming conventions, or reflecting a shift in the business logic or data representation that the column accommodates. Renaming helps maintain consistency and improves documentation, making the database easier to understand and use for developers and analysts .

The 'CREATE TABLE' command is used when initializing a new table in a database, defining its columns, and applying constraints. Constraints like PRIMARY KEY, FOREIGN KEY, and NOT NULL are essential for ensuring data integrity. Erroneous use of constraints can lead to functional issues, such as allowing duplicate entries when a PRIMARY KEY is incorrectly assigned, or data inconsistency when FOREIGN KEY relationships are not correctly defined, potentially leading to orphaned records and referential integrity violations .

The 'ORDER BY' clause in SQL specifies the sorting order of the retrieved data. It can order the result set by one or more columns in ascending (ASC) or descending (DESC) order. This allows for organized data viewing and is particularly useful in reporting and analysis because it helps users to quickly identify trends or specific values by sorting data in a meaningful way, such as sorting student records by their roll numbers or grades .

You might also like