JAI GURU DEV

MAHARISHI VIDYA MANDIR [Link],CHETPET,CH-31

DATABASE CONCEPTS and SQL

WORKSHEET
CLASS-XII

1.(i) Why is it not allowed to give string and date type argument for

Sum() and Avg() functions?

(ii) There is column C1 in a table T1.

The following two statements:

select count(*) from T1; and

select count(C1) from T1; are giving different output. What may be the possible
reason?

2 i) Which keyword is used to remove duplicate records from relation?

ii) A table "Design" in a database has 5 columns and 2 records. What is the
degree and cardinality of this table?

3. Differentiate between Primary Key and Foreign Key in RDBMS.

4. (i) Write the SQL Command to display all the rows from a table.

(ii) If there are ‘m’ rows in table-1 and ‘n’ rows in table-2, then how many
rows will be present in the Cartesian product of both the tables?

[Link] MYSQL database, if a table, SCHOOL has degree 4 and cardinality 5, and
another table,STUDENT has degree 4 and cardinality 3,What will be the degree
and cardinality of the Cartesian product of SCHOOL and STUDENT?

a) 4,5 b)4,3 c)8,15 d)16,8

[Link] is not a constraint in SQL?

a) unique b)distinct c)Primary key d)Check

[Link] the correct command to delete an attribute gender from a relation

student.

a)ALTER TABLE student DELETE gender

b)ALTER TABLE student DROP gender

c)ALTER TABLE DROP gender FROM student

d)DELETE gender FROM student

[Link] has just created a table named “sweets” containing columns Sname,
Quantity and Price.
After creating the table, she realized that she has forgotten to add a primary key
column in the table. Help her in writing an SQL command to add a primary key
column SID of integer type to the table sweets. Thereafter, write the command
to insert the following record in the table: SID- 999
Sname- Kaju Katli

Quantity : 40
Price : 2

9.a) In SQL, write the query to display the list of tables stored in a database.
(b) Sonal needs to display name of teachers, who have "O" as the third
character in their name. She wrote the following query.

SELECT NAME FROM TEACHER WHERE NAME="**O?"

But the query isn’t producing the result. Identify the problem.

10. (i) A table "Teachers" has three columns: TID, TNAME, and SUBJECT. Write a
SQL Command to add a new column in this table SALARY (of Integer type).

(ii) Categories the DDL and DML Commands from the following:

DROP, ALTER, SELECT, DELETE, INSERT, UPDATE

11. Which keyword is used to sort the records of a table in descending order?

12.(i) Which SQL logical operator is used to compare a value with a NULL value?

(ii) Which SQL logical operator is used to search for the presence of a row in a
specified table that meets a certain criterion?

13. Arvind is learning MySQL for managing different databases and tables for his
Python based application/software. Tell him the SQL commands for the
Following:-

(i) How to show pre-existing databases.

(ii) How to use a desired database

14 State True or False:

A primary key can store empty values in it:’

15. Which among following represent a logical relationship among a set of
values.
a. Attribute b. Key c. Tuple d. Field

16. Which of the following queries contains an error?

a. Select * From EMP where emp_id = 101

b. Select emp_id From EMP where emp_id = 102

c. Select emp_id From EMP

d. Select emp_id where emp_id = 109

17. Fill in the blank:

______ command is used to remove primary key from the table in SQL.
(a) update (b) remove (c) alter (d) drop

18. Differentiate between char(n) and varchar(n) data types with respect to
databases.

[Link] the full forms of DDL and DML commands. Write any 2 commands of
each in SQL.

20. _________is a non-key attribute, whose values are derived from the
primary key of some other table.

(A) Primary Key (B) Candidate Key (C) Foreign Key (D) Alternate Key

21. The SELECT statement when combined with clause, returns records without
repetition.

(A) DISTINCT (B) DESCRIBE (C) UNIQUE (D) NULL

[Link] of the following statements is True?

a) There can be only one Foreign Key in a table.

b) There can be only one Unique key in a table

c) There can be only one Primary Key in a Table

d) A table must have a Primary Key

23.A SQL table EMPLOYEE contains the following columns
ENO,ENAME,SALARY,DESIGNATION

Identify the command to remove the column designation from the table
EMPLOYEE
a)DELETE COLUMN DESIGNATION FROM EMPLOYEE

b) DROP COLUM DESIGNATION FROM EMPLOYEE

c)ALTER TABLE EMPLOYEE DROP COLUMN DESIGNATION’

d)UPDATE EMPLOYEE REMOVE DESIGNATION

[Link] is working on a database table storing details of medicinal plants of

different [Link] needs to display the number of plants of each [Link] a team
leader,suggest asuitable function and clause to used by him from the following
options given.

a)sum(),group by b)count(),group by c)avg(),having d)count(),distinct

[Link] the appropriate clause to arrange the elements in a sorted form.

a)order b)sort by c)order by d)arrange by

[Link] needs to display the name of the teachers,who have ‘i’ as the last
character in their [Link] wrote the following query.
SELECT NAME FROM TEACHER WHERE NAME=’’i’’;
But the query isn’t producing the [Link] the problem and correct the
query.

[Link] is alternate key?.Explain with an example.

[Link] are constraints?Illustrate with an example.

[Link] in the blank: ________ command is used to change table structure in

SQL.

(A) update (B) change (C) alter (D) modify

[Link] of the following commands will remove the entire database from
MYSQL?
(A) DELETE DATABASE (B) DROP DATABASE (C) REMOVE DATABASE (D) ALTER
DATABASE

31. _____ is a non-key attribute, whose values are derived from the primary key
of some other table.

(A) Primary Key (B) Candidate Key (C) Foreign Key (D) Alternate Key

32. The SELECT statement when combined with ________ clause, returns
records without repetition.
(A) DISTINCT (B) DESCRIBE (C) UNIQUE (D) NULL

33. Which function is used to display the total number of records from a table in
a database?

(A) total() (B) total(*) (C) return(*) (D) count(*)

34. Differentiate between order by and group by clause in SQL with appropriate
example.
35. Categorize the following commands as DDL or DML:

INSERT, UPDATE, ALTER, DROP

36. Which of the following is not part of a DDL query?

a) DROP b) MODIFY c) DISTINCT d) ADD

37. _______________ Keyword is used to obtain unique values in a SELECT

query

a) UNIQUE b) DISTINCT c) SET d) HAVING

38. Which statement in MySql will display all the tables in a database?

a) SELECT * FROM TABLES; b) USE TABLES; c) DESCRIBE TABLES; d) SHOW

TABLES
39. Which of the following is a valid sql statement?
a) ALTER TABLE student SET rollno INT(5);

b) UPDATE TABLE student MODIFY age = age + 10;

c) DROP FROM TABLE student;

d) DELETE FROM student;

40. A MySQL table, sales have 10 rows.

The following queries were executed on the sales table.

SELECT COUNT(*) FROM sales;

COUNT(*)
10

SELECT COUNT(discount) FROM sales;

COUNT(discount)
10

Write a statement to explain as to why there is a difference in both the counts.

SECTION B

[Link] the tables given below:

TABLE:ACCOUNT

Accno Name Acctype

1000 AKSHAYA SAVINGS
1001 BHARATH CURRENT
TABLE:BRANCH

BCODE CITY
B01 CHENNAI
B02 COIMBATORE
Name the operation performed after the query is executed:

SELECT * FROM ACCOUNT,BRANCH;

[Link] the questions based on the following table:

BNO TITLE AUTHOR TYPE PUB QTY PRICE

1 Data Lipschutz DS McGraw 4 217
Structure
2 Computer French FND Galgotia 2 75
Studies
3 Advanced Schildt PROG McGraw 4 350
Pascal
4 Dbase Palmer DBMS Pustak 5 130
dummies
5 Mastering Gurewich PROG BPB 3 295
C++
6 Guide Freed NET Zpress 3 200
Network
7 Mastering Seigal DBMS BPB 2 135
Foxpro
8 DOS Norton OS PHI 3 175
Guide
9 Basic for Norton PROG BPB 3 40
Beginners
10 Mastering Cowart OS BPB 1 225
Windows

a) What is the degree and cardinality of the above table?

b) When 2 rows are added and 3 attributes are deleted from the table what
is the cardinality of the new table..Sita

3. Write output of the queries (i) to (iii) based on the table

TABLE:SPORTS

Playerid Pname Sports Country Rating Salary

10001 PELE SOCCER BRAZIL A 50000
10002 FEDERER TENNIS SWEDEN A 20000
10003 VIRAT CRICKET INDIA A 15000
10004 SANIA TENNIS INDIA B 5000
10005 NEERAJ ATHLETICS INDIA A 12000
10006 BOLT ATHLETICS JAMAICA A 8000
10007 PAUL SNOOKER USA B 10000

(i)SELECT DISTINCT sports FROM Sportsclub;

(ii) SELECT sports, MAX(salary) FROM Sportsclub GROUP BY sports HAVING

sports<>'SNOOKER';

(iii) SELECT pname, sports, salary FROM Sportsclub WHERE country='INDIA'

ORDER BY salary DESC
4. Consider the table FASHION given below, write SQL queries :

Table: FASHION

ID PRODUCT PRICE QTY

F01 Kajal 970 10
F02 Foundation 2100 15
F03 Night Cream 1700 20
F04 Day Cream 1400 10
F05 Shampoo 1200 25
F06 Lipstick 850 32

a) Increase the price of Kajal by 5%

b) Display product and price of all products whose qty is more than 20 and
product name contains the character ‘s’.
c) Delete the details of all product whose price is more than 1200.
5. Consider the tables LAB and TRADER given below :

Write SQL queries for the questions (i) to (iv)

LAB

LabNo ItemName CostPerItem Quantity Dateofpurchase Warranty

1 Computer 60000 9 21/05/1996 1
2 Printer 15000 3 21/05/1997 4
3 Scanner 18000 1 29/08/1998 1
4 Camera 21000 2 13/10/1996 1
5 Switch 8000 1 31/10/1999 2
6 UPS 5000 5 21/05/2000 1
7 Router 25000 2 11/01/2000 2
8 Repeater 12000 4 10/02/1998 2

TRADER

LNO TraderName City

3 Busy Store Corp Mumbai
1 Electronic show Chennai
5 Busy Store Corp Bangalore
8 Sale Corp Chennai
2 Electronic Show Delhi

i)To display all the itemName whose name starts with “C” in Chennai city.

(ii) To list the ItemName in ascending order of the date of purchase where
quantity is more than 3.

iii)To update the ItemName to “Web Cam” where Itemname ends with ‘a’.
 (iv) To display the Itemname, Quantity and TraderName of an item purchased
after 01/01/1998.
6. Write the output of the queries (a) to (d) on the table, SCHOOL given below:

TABLE:SCHOOL

CODE TEACHER SUBJECT DOJ PERIODS EXP

1001 RAVI SHANKAR ENGLISH 12/3/2000 24 10
1009 PRIYA RAI PHYSICS 03/9/1998 26 12
1203 LIS ANAND ENGLISH 09/4/2000 27 5
1045 YASHRAJ MATHS 28/8/2000 24 15
1123 GAGAN PHYSICS 16/7/1999 28 3
1167 HARISH B CHEMISTRY 19/10/1999 27 5
1215 UMESH PHYSICS 11/5/1998 22 16

(a) SELECT MAX(EXP) FROM SCHOOL;

(b) SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE>12 ORDER BY

TEACHER;

(c) SELECT SUM(PERIODS) FROM SCHOOL WHERE SUBJECT LIKE "E%";

(d) SELECT SUBJECT FROM SCHOOL WHERE PERIODS<25 AND EXP>15 ;

7. Consider the table, CUSTOMER given below:

Table:Customer

CNO CNAME ADDRESS

101 RICHA JAIN DELHI
102 SURBHI SINHA CHENNAI
103 LISHA THOMAS BANGALORE
104 IMRAN ALI DELHI
105 ROSHAN SINGH CHENNAI

Table:Transactions

TNO CNO AMOUNT TYPE DOT

T001 101 1500 Credit 2007-11-23
T002 103 2000 Debit 2007-05-12
T003 102 3000 Credit 2007-06-10
T004 103 12000 Credit 2007-09-12
T005 101 1000 Debit 2007-09-05

(a) Identify the degree and cardinality of the above table.

(b) Which field should be made the primary key?

(c) Identify the candidate key(s) from the table CUSTOMER.

(d) Which command is used to add new column into the table CUSTOMER
8. Sahil has to create a database named KNOWLEDGE in MYSQL.

He now needs to create a table named student in the database to store the
records.
The table STUDENT has the following structure:

FIELD NAME DATATYPE REMARKS

ADMNO INTEGER PRIMARY KEY
NAME CHAR(20) NOT NULL
CLASS INTEGER
SEC CHAR(2)
RNO INTEGER
ADDRESS VARCHAR(30)

Help him to complete the task by suggesting appropriate SQL commands.

9.

10.

11.
12.
13.

14.

15.
16.
17.

18.
19.
20.

21.
22.

Based on the above table

(iii) Write the degree and cardinality.

(iv) If one column named Fees added in the below given table, then write the
degree and cardinality of the table after adding.

23.
[Link] on the given set of tables write answers to the following questions.

[Link] the output of the queries (a) to (d) based on the table COURSE given
below: RELATION:COURSE

NO NAME SUBJECT FEES AGE GENDER

1 KARTHIK MATHS 1000 17 M
2 RAKESH COMPUTER 1200 16 M
3 GEETHA NULL 900 18 F
4 JANSI MATHS 1200 17 F
5 PANKAJ COMPUTER NULL 18 M
6 SRINIVAS ENGLISH 1100 17 M
7 DEEPIKA NULL 750 16 F
8 SWATI BIOLOGY 1250 18 F
a)SELECT NAME,FEES FROM COURSE WHERE NAME LIKE ‘%S%’;
b)SELECT DISTINCT SUBJECT FROM COURSE;

c)SELECT AGE,COUNT(*),MAX(FEES) FROM COURSE GROUP BY AGE HAVING

COUNT(AGE)>2;
d)SELECT AVG(FEES) FROM COURSE WHERE SUBJECT=’COMPUTER’ OR
SUBJECT=’BIOLOGY’;

26. Write the output of the queries (i) to (vi) based on the table given below

(i) Select BRAND_NAME, FLAVOUR from CHIPS where PRICE <> 10;

(ii) Select * from CHIPS where FLAVOUR=”TOMATO” and PRICE > 20;

(iii) Select BRAND_NAME from CHIPS where price > 15 and QUANTITY < 15;
(iv) Select count( distinct (BRAND_NAME)) from CHIPS;

(v) Select price , price *1.5 from CHIPS where FLAVOUR = “PUDINA”;

(vi) Select distinct (BRAND_NAME) from CHIPS order by BRAND_NAME desc;

27. (A) Consider the following tables BOOKS and ISSUED in a database named
“LIBRARY”. Write SQL commands for the statements (i) to (iv).

(i) Display book name and author name and price of computer type books.
(ii) To increase the price of all history books by Rs 50.

(iii) Show the details of all books in ascending order of their prices.
(iv) To display book id, book name and quantity issued for all books which have
been issued.

(V) Write the command to view all tables in a database

28. Layna creates a table STOCK to maintain computer stock in vidyalaya. After
creation of the table, she has entered data of 8 items in the table.

Based on the data given above answer the following questions:

(i) Identify the most appropriate column, which can be considered as Primary
key.
(ii) If three columns are added and 5 rows are deleted from the table stock,
what will be the new degree and cardinality of the above table?
(iii) Write the statements to:
(a) Insert the following record into the table Stockid - 201, dateofpurchase – 18-
OCT-2022, name – neckphone Make – BoAT, price – 500

(b) Decrease the price of stock by 5% whose were purchased in year 2020 OR
(Option for part iii only)
(iv) Write the statements to:

(a) Delete the record of stock which were purchased before year 2015.

(b) Add a column STATUS in the table with datatype as char with 1 character

29. a) Consider the following tables Emp and Dept:

Relation: Emp

Relation:dept

What will be the output of the following statement?

SELECT * FROM Emp NATURAL JOIN Dept WHERE dname='Physics';

[Link]:EMPLOYEE

EMPID NAME DEPT GENDER

101 KUTTAN SALES MALE
102 SUBASH MARKETING MALE
103 ABHILASHA SALES FEMALE
TABLE:SALARY

EMPID SALARY
101 10000
102 NULL
103 20000
I)SELECT * FROM EMPLOYEE NATURAL JOIN SALARY;

II)SELECT * FROM EMPLOYEE ,SALARY WHERE

[Link]=[Link];
WHAT IS THE DEGREE AFTER EXECTING THE ABOVE QUERIES?31.

Create a table Subject with the following attributes

ROLL integer References with

Roll of the table
Student
SUBCODE integer not null
SUBJECT varchar(20) not null

32.

TABLE:EMP

NAME SALARY
SARVESH 10000
EKNA NULL
MOHAMMED 20000

WRITE THE OUTPUT FOR THE FOLLOWING QUERY:

SELECT AVG(SALARY) FROM EMP;

33. Consider the table

“Coaching”.

ID NAME AGE CITY FEE PHONE

P1 SAMEER 34 DELHI 45000 9811076656
P2 ARYAN 35 MUMBAI 54000 9911343989
P4 RAM 34 CHENNAI 45000 9810593578
P6 PREMLATA 36 BHOPAL NULL 9910139987
 P7 SHIKHA 36 INDORE 34000 9912139456
P8 RADHA 33 DELHI NULL 8110668888

a. To select tuples which contains fee except the null values, Arun has
written the following erroneous SQL statement: SELECT ID, FEE
FROM Coaching WHERE FEE=something; Rewrite the SQL
statement with correct condition.
b. Is the following statement correct or not? If not, rectify the error and
evaluate it.
Delete NAME from Coaching;