Share with others

Table of Contents

SQL Practice – Assignment 1

SQL Practice – Assignment 2

SQL Practice – Assignment 3

SQL Practice – Assignment 4

CHAPTER : SQL Practice Questions

ASSIGNMENT SET – 1

Time: 30 min M.M. – 20

Instructions:

All Questions are compulsory
Q1 to Q6 carry 1 mark
Q7 to Q10 carry 2 marks
Q11 carry 6 marks

Q1. Give two examples of DDL commands.

Q2. What is primary key?

Q3. Name the command which is used to view the structure of the table.

Q4. Records in MySQL are also known as __________.

Q5. Vertical columns in table are called ________(Record/Attribute)

Q6. Define table in MySQL.

Q7. What do you mean by constraint? Give two examples of constraint.

Q8. What do you mean by degree and cardinality of table? What is the degree and cardinality of the following table?

Bookid	Bname	Price(Rs)
A101	ABC	120
A102	ADF	200

SQL Practice Questions

Q9. Write one difference and one similarity between primary key and unique constraint.

Q10. Define the following terms:

Candidate Key
Alternate Key

Q11. Write SQL commands for (i) to (iv) and write the output of (v) and (vi).

Table : Bank

Bank_id	Cust_name	Bank_name	Amount	transaction
1	Amit	State Bank	20000	25
2	Sunil	Union Bank	25000	20
3	Nidhi	UCO Bank	18000	15
4	Ashish	Union Bank	10000	22
5	Suman	State Bank	18500	8

SQL Practice Questions

Display details of those customers whose transactions are more than 20.
Display Customer Name and Amount of all the customers of State Bank.
Display the total number of customers of Union Bank.
Display details of customers whose name contains ‘i’.
Select sum(Amount) from Bank;
Select count(*) from Bank.

CHAPTER : SQL Practice Questions

ASSIGNMENT SET – 2

Time: 30 min M.M. – 20

Instructions:

All Questions are compulsory
Q1 to Q6 carry 1 mark
Q7 to Q10 carry 2 marks
Q11 carry 6 marks

Q1. What is DBMS?

Q2. The number of tuples in a table is called _________.

Q3. Write any four possible attributes of table “Book”.

Q4. Views are subset of table.(T/F)

Q5. What is the degree and cardinality of table which is obtained after the cartesian product of Table1 and Table2?

Table1 degree and cardinality is 2 and 3 respectively.

Table2 degree and cardinality is 4 and 5 respectively.

Q6. Define the term data redundancy in reference to MySQL.

Q7. Differentiate between char and varchar data type.

Q8. Identify the DDL and DML commands from the following:

Drop
Delete
Update
Insert

Q9. What is the difference between alter and update command. Explain with example.

Q10. Write two advantages of MySQL.

Q11. Write SQL commands for (i) to (iv) and write the output of (v) and (vi).

Table : product

p_id	p_price	p_name	p_warranty	p_qty
1	35000	Washing Machine	3	10
2	45000	Smart TV	2	5
3	33000	AC	5	6
4	20000	Home Theatre	1

SQL Practice Questions

Display details of product whose warranty is more than 2 years.
Display all details of Smart TV.
Display the price of those products whose quantity is Null.
Display sum of the price of all products.
Select avg(p_qty) from product;
Select distinct(p_name) from product;

CHAPTER : SQL Practice Questions

ASSIGNMENT SET – 3

Time: 30 min M.M. – 20

Instructions:

All Questions are compulsory

Q1	In SQL order by clause is used to arrange record in _____ order. a. only ascending b. only descending c. either ascending or descending	1
Q2.	What is the use of distinct clause in MySQL?	1
Q3.	Give two examples of DDL command.	1
Q4.	What do you mean by degree in MySQL?	1
Q5	Write command to create the following table : Book Field Name Datatype Constraint BNo Integer(4) primary Key Bname Varchar(20) Author Varchar(30) Price float(4,2)	2
Q6	Write the query for the following a. To delete a column “Bname” from table “Book” in MySQL. b. To display records of table “Book” in descending order of price(field name “bprice”)	2
Q7	Write the output of the following query (i) and also write queries for (ii-iii) on the basis of following tables: Table : Employee Emp_id Emp_Sal Emp_desig Emp_name 1 20000 Manager Amod 2 15000 Clerk Anil Table : Job job_id city job_type Emp_id j1 Delhi Permanent 1 J2 Goa Temporary 2 1. Select Emp_Sal, Emp_name from Employee where Emp_desig in (“Manager”, “Clerk”) 2. Display the names of employees who lives in Goa. 3. Display total salary of all clerk.	3
Q8.	Write SQL commands for the following: Table : Teacher TID TName TSal TDept TDesig 1 Amit 2000 IT PGT 2 Sumit 1500 History TGT 3 Naina 1800 Math PGT a. Write the degree and cardinality of Table : Teacher b. Identify the primary key in given Table. c. Dislay records of all PGT Staff d. Increase the salary of teachers of Math department.	4
Q9.	Write SQL commands for the following queries (i) to (v) based on the relations Teacher and Student given below: Teacher : T_id T_name T_sal T_desig Student : Adm_no S_name S_class T_id Display the details of teachers whose name starts from ‘A’ Display the count of teachers who received salary more than 24000. Display the details of teachers who received maximum and minimum salary. Display the teacher name, teacher salary who teaches class IX. Display the average salary of teachers.	5

SQL Practice Questions

CHAPTER : SQL Practice Questions

ASSIGNMENT SET – 4

Time: 30 min M.M. – 20

Instructions:

All Questions are compulsory

Q1	Find the error in the following query: Select * from table student;	1
Q2.	Name the keyword used to eliminate duplicate records.	1
Q3.	Give two examples of DML command.	1
Q4.	Suman created a table in MySQL. Degree and Cardinality of the table is 4 and 5 respectively. How many tuples and attributes are there in the table?	1
Q5	Write command to create the following table : Student Field Name Datatype Constraint Admno Integer(4) primary Key Name Varchar(20) Fees float(7,2) DOJ Date	2
Q6	Write the query for the following a. To display sum of column fees from table student. b. To display records of all students who born in year 1998 (Table name : Student, Field Name : DOB)	2
Q7.	Differentiate between Alter and Update Command in MySQL	2
Q8.	Write SQL queries for (i) to (vii), which are based on the following table. Table: Candidate T_no Name Mobile City Sex Date Fees 101 Mr. Kumar 3562542 Delhi M 23-02-2020 2500 102 Mr. Pathak 3265412 Noida M 12-05-2020 3000 103 Ms. Manisha 3528978 Ghaziabad F 13-09-2020 3000 104 Ms. Sonam 3269852 Delhi F 12-03-2021 1500 a. Display all female candidates from the table. b. Display the count of all the candidates from Delhi. c. Display all the records in increasing order of their fees. d. Insert a new column “Email” of suitable data type. e. Increase the fees by Rs 500 of all male candidates. f. Display the sum of fees paid by female candidates. g. Display mobile number, City and fees of those candidates whose name ends with ‘k’ h. Display the mobile number of “Ms. Manisha” i. Display the total fees paid by candidates of Delhi. j. Display unique city from the table.	10