DBMS SAMPLE QUESTION SOLUTION

 Q.1. Create Table for order.

a. Add fields: name, id, author, pages, publication,

and price.

b. Insert minimum 10 records.

c. Select id, name and price from

order table.

d. Truncate the table.

To truncate the orders table, you can use the following SQL command:

This command will remove all rows from the orders table while keeping the table structure intact. Make sure to use it with caution as it deletes all data in the table.

e. Drop the table.

To drop the orders table, you can use the following SQL command:

This command will completely remove the orders table from the database, including its structure and all associated data. Use it with caution as it cannot be undone.

Q2 QUESTION

i. Find total number of students in stud table.

ii. Find total of marks scored by all students.

iii. Find average marks of students.

iv. Find minimum age of student.

v. Find maximum marks of students.

Q3. Create the following table EMPL_01:

a) Display the employee name along with their salay.

b) Display the total number of employees working as a ‘salesperson’.

c) Display employee details whose salary lies between 100000 to 650000.

d) Display employee details who is working in brach ‘b4’.

4. Perform the following:

a. Viewing all databases

b. Creating a Database

c. Viewing all Tables in a Database

d. Creating Table for customer (With Constraints)

SYNTAX:

CREATE TABLE table_name (

    column1 datatype constraints,

    column2 datatype constraints,

    ...

);

e. Inserting minimum 10 records.

SYNTAX:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

f. Updating minimum 5 records.

SYNTAX :

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

In this example:

• Each UPDATE statement updates the age column for a specific customer identified by their customer_id.
• You would repeat these UPDATE statements for different records or different columns as needed to update at least 5 records in the customers table. Adjust the condition (WHERE clause) and the value being updated according to your specific requirements.

g. Deleting at least 2 records in a Table.

SYNTAX:

DELETE FROM table_name

WHERE condition;

h. Add two column.

SYNTAX:

ALTER TABLE table_name

ADD COLUMN new_column datatype constraints;

i. Rename the table name.

SYNTAX :

ALTER TABLE old_table_name RENAME TO new_table_name;

5. Perform the following function:

a) ABS ( ) b) SQRT( ) c) current date and time d) ROUND ( ) f) Current date f) MOD ( ) g) SIGN () h) POWER( )

a) ABS(): Returns the absolute value of a number.

b) SQRT(): Returns the square root of a number

c) Current Date and Time: Returns the current date and time.

d) ROUND(): Rounds a number to a specified number of decimal places.

e) Current Date: Returns the current date.

f) MOD(): Returns the remainder of a division operation.

g) SIGN(): Returns the sign of a number (-1 for negative, 0 for zero, 1 for positive).

h) POWER(): Raises a number to a specified power.

6. Create a table Student with following columns.

(10 Records)

(Rollno primary key, Fname, Lname, Course_name,

Email_id)

a) Display the total number of student enrolled in

BAF course.

b) Display the student names whose name start

with letter ‘A’.

c) Display students first name and last name.

d) Display student details whose rollno is 208.

e) Display the student name who enrolled in the

course ‘BCom’.

7. Perform the following aggregate function:

a)AVG ( ) b) MIN ( ) c) MAX ( ) d) SUM( ) e) COUNT()

let's perform each aggregate function on the Student table:

a) AVG(): Calculate the average of a numeric column:

b) MIN(): Retrieve the minimum value of a column:

c) MAX(): Retrieve the maximum value of a column.

d) SUM(): Calculate the sum of values in a numeric column:

e) COUNT(): Count the number of rows in a table:

8. Views: Creating View , Selecting View, Drop

View

In SQL, a view is a virtual table that is based on the result set of a SELECT statement. It behaves like a table in that you can query data from it using SELECT statements, but unlike a table, it does not store any data itself. Instead, it is a saved query that is stored in the database and can be referenced and used like a table.

Creating a View:

2. Selecting from a View:
3. Once you've created a view, you can select data from it just like you would from a table:
4. 
   
   
   3. Dropping a View:
   4. To remove a view from the database:
   5. 
      
      Overall, views provide a way to present data in a structured format without the need to duplicate or modify the underlying data