CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL)

MULTIPLE CHOICE QUESTIONS (MCQs)

1. What does SQL stand for?

a) Structured Question Language
b) Simple Query Language
c) Structured Query Language
d) System Query Language
Answer: c
2. Which of the following is not a type of RDBMS?
a) MySQL
b) Oracle
c) MongoDB
d) PostgreSQL
Answer: c

3. SQL is case sensitive in which environment?

a) Windows
b) Linux
c) Mac
d) Android
Answer: b

4. Which of the following is a correct SQL statement to create a database named Library?
a) MAKE DATABASE Library;
b) NEW DATABASE Library;
c) CREATE DATABASE Library;
d) DATABASE Library;
Answer: c
5. Which command is used to list all databases in MySQL?
a) LIST DATABASES;
b) SHOW ALL DATABASES;
c) DISPLAY DATABASES;
d) SHOW DATABASES;
Answer: d

6. What is the purpose of the ‘USE’ command in SQL?

a) Create a new table
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) Delete a database
c) Select a database for use
d) Exit SQL
Answer: c
7. Which of the following is a valid datatype for storing dates in MySQL?
a) DATE
b) DATETIME
c) TIME
d) All of the above
Answer: d

8. CHAR(n) datatype in MySQL stores data of:

a) Fixed length
b) Variable length
c) Integer
d) Binary type
Answer: a

9. Which constraint ensures uniqueness of values in a column?

a) PRIMARY KEY
b) FOREIGN KEY
c) UNIQUE
d) DEFAULT
Answer: c
10. Which two constraints together can form a primary key?
a) UNIQUE and DEFAULT
b) NOT NULL and UNIQUE
c) FOREIGN KEY and UNIQUE
d) NOT NULL and DEFAULT
Answer: b
11. What does the ALTER TABLE command do in SQL?
a) Deletes a table
b) Changes the structure of a table
c) Adds data to a table
d) Queries data
Answer: b

12. Which keyword is used to remove a column from a table?

a) REMOVE
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) DELETE
c) DROP
d) ALTER
Answer: c
13. Which command is used to view the structure of a table?
a) SHOW tablename
b) DESCRIBE tablename
c) VIEW tablename
d) PRINT tablename
Answer: b

14. What type of key is formed by combining multiple attributes?

a) Foreign key
b) Alternate key
c) Composite key
d) Candidate key
Answer: c

15. What happens if you run an UPDATE statement without a WHERE clause?
a) Nothing happens
b) Syntax error
c) All records are updated
d) Only the first record is updated
Answer: c
16. Which statement is used to insert data into a table?
a) ADD INTO
b) INSERT INTO
c) PUT INTO
d) ENTER INTO
Answer: b
17. In which format is the DATE stored in MySQL?
a) DD-MM-YYYY
b) MM-DD-YYYY
c) YYYY-MM-DD
d) Any format
Answer: c

18. Which operator is used to compare a value with a set of values?

a) IN
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) LIKE
c) BETWEEN
d) ALL
Answer: a
19. Which SQL function returns the remainder of a division operation?
a) DIV
b) REM
c) MOD
d) ROUND
Answer: c

20. Which function is used to calculate exponential power in SQL?

a) EXP()
b) ROUND()
c) POW()
d) SQRT()
Answer: c

21. Which clause is used to sort records in ascending or descending order?

a) GROUP BY
b) ORDER BY
c) SORT BY
d) ARRANGE BY
Answer: b
22. Which keyword is used with ORDER BY to sort in descending order?
a) REVERSE
b) DOWN
c) DESC
d) LOWER
Answer: c
23. Which function returns the name of the day from a date?
a) DAYNAME()
b) DAY()
c) DATENAME()
d) DAYOFWEEK()
Answer: a

24. Which clause helps in fetching only unique values from a column?
a) ONLY
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) UNIQUE
c) DISTINCT
d) NON-REPEATED
Answer: c
25. What is the result of any arithmetic operation with NULL?
a) 0
b) NULL
c) Error
d) Infinity
Answer: b

26. Which function returns current date and time in SQL?

a) CURRENT()
b) NOW()
c) TIME()
d) SYSDATE()
Answer: b

27. Which of the following is used for pattern matching in SQL?

a) LIKE
b) IN
c) AND
d) IS
Answer: a
28. Which symbol is used to match any number of characters in SQL pattern matching?
a) _
b) %
c) *
d) #
Answer: b
29. Which SQL function removes leading spaces from a string?
a) RTRIM()
b) LTRIM()
c) TRIM()
d) REMOVE()
Answer: b

30. Which function converts string to uppercase in SQL?

a) UPPER()
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) UCASE()
c) TOUPPER()
d) CAP()
Answer: b
31. Which clause is used to group rows that have the same values?
a) ORDER BY
b) GROUP BY
c) SET BY
d) UNION
Answer: b

32. Which function returns the number of characters in a string?

a) LEN()
b) LENGTH()
c) SIZE()
d) COUNT()
Answer: b

33. Which clause is used to filter rows before grouping?

a) WHERE
b) HAVING
c) FILTER
d) SELECT
Answer: a
34. Which clause is used to filter groups after grouping?
a) WHERE
b) HAVING
c) GROUP BY
d) FILTER
Answer: b
35. Which aggregate function returns the highest value in a column?
a) MAX()
b) TOP()
c) LAST()
d) HIGH()
Answer: a

36. Which clause in SQL is used to group records that have the same values in specified columns?
a) ORDER BY
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) GROUP BY
c) WHERE
d) HAVING
Answer: b
37. Which operator is used to test for NULL values?
a) = NULL
b) == NULL
c) IS NULL
d) EQUAL NULL
Answer: c

38. What will be the result of 5 + NULL in SQL?

a) 5
b) 0
c) NULL
d) Error
Answer: c

39. Which function extracts a substring from a string?

a) MID()
b) SUBSTRING()
c) SLICE()
d) CUT()
Answer: a
40. What is the output of ROUND(123.456, 1)?
a) 123.4
b) 123.5
c) 123.45
d) 123
Answer: b
41. Which keyword is used to permanently remove a table from a database?
a) ERASE
b) DELETE
c) REMOVE
d) DROP
Answer: d

42. What is the output of MOD(11, 3)?

a) 2
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) 3
c) 1
d) 0
Answer: a
43. What is the purpose of the DEFAULT constraint?
a) Allows duplicate values
b) Adds a default value when no input is given
c) Restricts NULL values
d) Sets a foreign key
Answer: b

44. Which operator is used to match a value within a range?

a) IN
b) RANGE
c) BETWEEN
d) BOUND
Answer: c

45. Which one is not a single row function?

a) ROUND()
b) MOD()
c) AVG()
d) LCASE()
Answer: c
46. Which function is used to get the month from a date?
a) MONTHNAME()
b) MONTH()
c) GETMONTH()
d) MTH()
Answer: b

47. Which of the following is a valid constraint that can be applied to enforce referential integrity
between two tables?
a) DEFAULT
b) PRIMARY KEY
c) UNIQUE
d) FOREIGN KEY
Answer: d

48. Which of the following conditions must be satisfied for a FOREIGN KEY to be added to a
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

table?
a) The table must have a DATE column
b) The referenced attribute must be of FLOAT type
c) The referenced column must be a PRIMARY KEY
d) The referenced table must be empty
Answer: c

49. Which SQL operator is used to check whether a column value falls between two values (inclu-
sive)?
a) IN
b) LIKE
c) RANGE
d) BETWEEN
Answer: d

50. Which keyword is used to delete a particular row in SQL?

a) REMOVE
b) ERASE
c) DELETE
d) DROP
Answer: c

51. Assertion (A): SQL is used only for retrieving data from databases. Reason (R): SQL stands
for Structured Query Language.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false
Answer: c

52. Assertion (A): VARCHAR stores variable-length character strings. Reason (R): CHAR and
VARCHAR allocate the same amount of memory for any string.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: c

53. Assertion (A): PRIMARY KEY allows duplicate values in a column. Reason (R): PRIMARY
KEY is used to uniquely identify each record.
a) Both A and R are true, and R is the correct explanation of A
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b) Both A and R are true, but R is not the correct explanation of A

c) A is false, but R is true
d) A is true, but R is false
Answer: c

54. Assertion (A): ALTER TABLE statement can be used to add a new column. Reason (R): ALTER
TABLE can modify the structure of an existing table.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false
Answer: a

55. Assertion (A): The keyword DESC is used to sort records in descending order. Reason (R): The
ORDER BY clause displays records in random order by default.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: c

56. Assertion (A): SQL is a case-insensitive language. Reason (R): In SQL, ‘salary’ and ‘SALARY’
are treated as the same column names.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false
Answer: a

57. Assertion (A): The CREATE TABLE command is used to define the structure of a new table.
Reason (R): CREATE TABLE can define attribute names, data types, and constraints.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: a

58. Assertion (A): DEFAULT constraint ensures that every column must have a unique value.
Reason (R): DEFAULT sets a predefined value for a column when no value is provided.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

c) A is false, but R is true

d) A is true, but R is false
Answer: c

59. Assertion (A): DELETE statement removes a table from the database. Reason (R): DROP
TABLE statement is used to remove a table permanently.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false
Answer: c

60. Assertion (A): The WHERE clause in SQL is used to filter groups formed by GROUP BY.
Reason (R): HAVING clause filters records before they are grouped.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) Both A and R are false
Answer: d

61. Assertion (A): INSERT INTO command adds new records into a table. Reason (R): Records
can be inserted without specifying column names if all values are provided in correct order.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: a

62. Assertion (A): A FOREIGN KEY can reference a column that is not a primary key. Reason
(R): A foreign key ensures that each value exists in another table’s primary key column.
a) Both A and R are true, and R is the correct explanation of A
b) A is true, but R is false
c) A is false, but R is true
d) Both A and R are false
Answer: c

63. Assertion (A): ROUND() is used to round numbers to specified decimal places. Reason (R):
ROUND(283.2, 0) will return 283.2.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

d) A is false, but R is true

Answer: c

64. Assertion (A): The % symbol in SQL is used to match zero or more characters. Reason (R):
The LIKE clause in SQL supports pattern matching using wildcards.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: a

65. Assertion (A): The BETWEEN operator is inclusive of boundary values. Reason (R): BE-
TWEEN is used to test if a value lies between a specified range.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: a

66. Assertion (A): You can use the alias keyword AS to rename a column in the output. Reason
(R): Column aliasing affects the actual table structure permanently.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: c

67. Assertion (A): NOW() function returns the current system date and time. Reason (R): NOW()
is a date function used in MySQL.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false
Answer: a

68. Assertion (A): MID() function is used to extract a part of a string. Reason (R): MID() is a
numeric function used to perform arithmetic operations.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

Answer: c

69. Assertion (A): The TRIM() function is used to remove trailing spaces only. Reason (R): TRIM()
removes both leading and trailing spaces from a string.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: d

70. Assertion (A): The IS NULL operator is used to check for zero values. Reason (R): IS NULL
checks whether a column has NULL as its value.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false
Answer: c

FILL IN THE BLANKS

1. SQL stands for .

Answer: Structured Query Language

2. The clause used to sort records in SQL is .

Answer: ORDER BY

3. The key uniquely identifies each record in a table.

Answer: Primary
4. To insert new records in a table, we use the statement.
Answer: INSERT INTO
5. In MySQL, date values are stored in the format .
Answer: YYYY-MM-DD

6. The clause is used to filter records before grouping.

Answer: WHERE

7. The clause is used to filter grouped records.

Answer: HAVING

8. The wildcard character represents zero or more characters in SQL.

Answer: %
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

9. To display all columns from a table, we use the symbol in a SELECT statement.
Answer: *

10. The command to remove an entire table from the database is .

Answer: DROP TABLE
11. To change the structure of a table, we use the statement.
Answer: ALTER TABLE
12. The constraint ensures a column cannot have NULL values.
Answer: NOT NULL
13. The function returns the current date and time.
Answer: NOW()

14. To define a column with fixed length string data, we use the datatype .
Answer: CHAR
15. The datatype stores numeric values with decimal points.
Answer: FLOAT

16. The function is used to round off numbers in SQL.

Answer: ROUND()

17. is a special value in SQL used to represent missing or unknown information.

Answer: NULL

18. The constraint automatically fills in a column with a predefined value when no
input is given.
Answer: DEFAULT
19. To rename a column in the output of a SELECT query, we use the keyword .
Answer: AS
20. The operator is used to check if a column value is within a set of values.
Answer: IN
21. The function returns the number of characters in a string.
Answer: LENGTH()

22. The function removes both leading and trailing spaces from a string.
Answer: TRIM()

23. A composite primary key consists of or more columns.

Answer: two
24. The command is used to view the structure of an existing table.
Answer: DESCRIBE
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

25. The clause is used with SELECT to retrieve only unique values.
Answer: DISTINCT

26. The constraint ensures all values in a column are different.

Answer: UNIQUE

27. The operator is used to retrieve records where a column value lies within a specific
range.
Answer: BETWEEN

28. The function extracts a part of a string.

Answer: MID()

29. A column that refers to a primary key in another table is called a key.
Answer: foreign

30. The command permanently removes all records from a table and the table structure
itself.
Answer: DROP

2 MARKS QUESTIONS

1. What is SQL?
Answer: SQL stands for Structured Query Language. It is a language used to create, manipulate, and
retrieve data from relational databases like MySQL, Oracle, SQL Server, etc.

2. Why is SQL considered easy to learn?

Answer: SQL is considered easy because its statements are made up of descriptive English words and it
is case-insensitive.

3. What is the difference between CHAR and VARCHAR datatypes?

Answer: CHAR is a fixed-length datatype that pads unused characters with spaces, while VARCHAR is
a variable-length datatype that stores only the required number of characters.
4. What is the significance of constraints in SQL?
Answer: Constraints enforce rules on data in a table to ensure accuracy and integrity, such as NOT NULL,
UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT.

5. Give the syntax and an example to create a database named StudentAttendance.

Answer:
Syntax: CREATE DATABASE databasename;
Example: CREATE DATABASE StudentAttendance;
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

6. Differentiate between PRIMARY KEY and FOREIGN KEY.

Answer: A PRIMARY KEY uniquely identifies each record and does not allow NULLs, while a FOR-
EIGN KEY is used to link two tables and refers to the PRIMARY KEY of another table.

7. Write the syntax to create a table in SQL and give an example.

Answer:
Syntax:
CREATE TABLE tablename (column1 datatype constraint, column2 datatype constraint,
...);
Example:

CREATE TABLE STUDENT(

RollNumber INT PRIMARY KEY,
SName VARCHAR(20) NOT NULL,
SDateofBirth DATE,
GUID CHAR(12)
);

8. What is the use of the DESCRIBE command in SQL?

Answer: The DESCRIBE or DESC command is used to view the structure of a table including column
names, data types, and constraints.

9. What is a composite primary key? Give an example.

Answer: A composite primary key consists of two or more columns used together to uniquely identify a
record.
Example:
PRIMARY KEY (AttendanceDate, RollNumber);
10. Give an example to add a foreign key in the STUDENT table.
Answer:

ALTER TABLE STUDENT

ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

11. How do you add a new column to an existing table? Give syntax and example.
Answer:
Syntax: ALTER TABLE tablename ADD column_name datatype;
Example:
ALTER TABLE GUARDIAN ADD Income INT;

12. Differentiate between DELETE and DROP commands in SQL.

Answer: DELETE removes rows from a table based on a condition but retains the table structure, whereas
DROP permanently deletes the entire table and its structure.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

13. Write the syntax to insert data into a table and give an example.
Answer:
Syntax: INSERT INTO tablename VALUES (value1, value2, ...);
Example:
INSERT INTO STUDENT VALUES (1, 'Atharv Ahuja', '2003-05-15', 444444444444);
14. What is the use of the SELECT statement? Give a simple example.
Answer: SELECT is used to retrieve data from a table.
Example:
SELECT * FROM STUDENT;
15. What is the purpose of the DISTINCT clause in a SELECT query?
Answer: DISTINCT is used to return only unique (non-duplicate) values in the output of a SELECT
statement.

16. Give the syntax of the ORDER BY clause and state its default order.
Answer:
Syntax: SELECT * FROM tablename ORDER BY column_name [ASC|DESC];
Default order is ascending (ASC).

17. What is the difference between WHERE and HAVING clause?

Answer: WHERE filters rows before grouping, while HAVING filters groups formed by GROUP BY.

18. Write a query to display employee names and their annual income (Salary * 12), using column
aliases.
Answer:

SELECT Ename AS Name, Salary*12 AS 'Annual Income'

FROM EMPLOYEE;

19. What does the LIKE operator do in SQL? Give an example.

Answer: The LIKE operator is used for pattern matching using wildcards.
Example:
SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';
20. What are single-row functions? Give two examples.
Answer: Single-row functions operate on a single value and return a single value.
Examples: UCASE(), ROUND()

3 MARKS QUESTIONS

1. What are the main features of SQL? List any three.

Answer:
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

1. SQL is a non-procedural, declarative language.

2. It is used for creating and managing relational databases.
3. It is case-insensitive and easy to understand due to English-like syntax.

2. Differentiate between DDL and DML commands with one example each.
Answer:
DDL (Data Definition Language) defines the structure of a database. Example: CREATE TABLE.
DML (Data Manipulation Language) is used to manage data in tables. Example: INSERT INTO.
3. Write the syntax of the CREATE TABLE command and give an example to create a STUDENT
table with RollNumber, Name, and DateOfBirth.
Answer:
Syntax:

CREATE TABLE tablename (column1 datatype, column2 datatype, ...);

Example:

CREATE TABLE STUDENT (

RollNumber INT,
Name VARCHAR(25),
DateOfBirth DATE
);

4. Explain any three constraints used in SQL.

Answer:
1. NOT NULL – Ensures that a column cannot have NULL values.
2. UNIQUE – Ensures that all values in a column are different.
3. PRIMARY KEY – Uniquely identifies each record and does not allow NULLs.

5. What is a foreign key? Give its syntax and explain with an example.
Answer:
A foreign key is a column that refers to the primary key of another table.
Syntax:

FOREIGN KEY (column_name) REFERENCES other_table(column_name);

Example:

ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

6. Write a query to insert a record into the GUARDIAN table. Also, write a query to add a new
column Occupation.
Answer:
Insert:
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

INSERT INTO GUARDIAN VALUES (444444444444, 'Prithvi Raj', 'M', 'Doctor', 1200000);

Add column:

ALTER TABLE GUARDIAN ADD Occupation VARCHAR(30);

7. Write a query to update the salary of employee code 103 to 80000 and delete employee with code
104.
Answer:
Update:

UPDATE EMPLOYEE SET Salary = 80000 WHERE Ecode = 103;

Delete:

DELETE FROM EMPLOYEE WHERE Ecode = 104;

8. What is the difference between DELETE and DROP commands? Give one use case each.
Answer:
DELETE removes specific rows but retains the table structure.
Example: DELETE FROM STUDENT WHERE RollNumber = 1;
DROP removes the table structure entirely.
Example: DROP TABLE STUDENT;

9. Explain the use of SELECT statement with WHERE and ORDER BY clauses using an example.
Answer:
SELECT retrieves records from a table.
WHERE filters rows based on a condition.
ORDER BY sorts the results.
Example:

SELECT * FROM EMPLOYEE WHERE Salary > 40000 ORDER BY Ename;

10. Write a query using DISTINCT to display unique city names from the CUSTOMER table.
Also, explain its use.
Answer:
Query:

SELECT DISTINCT City FROM CUSTOMER;

Explanation: DISTINCT is used to remove duplicate values from the result set.

11. Differentiate between the WHERE and HAVING clauses with example queries.
Answer:
WHERE filters rows before grouping;
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

HAVING filters groups after grouping.

Example:

SELECT * FROM EMPLOYEE WHERE Salary > 30000;

SELECT DeptID, COUNT(*) FROM EMPLOYEE GROUP BY DeptID HAVING COUNT(*) > 2;

12. Write queries to demonstrate the use of LIKE operator with the patterns:
a) Names starting with ‘A’
b) Names ending with ‘n’
c) Names containing ‘sh’
Answer:
a) SELECT * FROM EMPLOYEE WHERE Ename LIKE 'A%';
b) SELECT * FROM EMPLOYEE WHERE Ename LIKE '%n';
c) SELECT * FROM EMPLOYEE WHERE Ename LIKE '%sh%';
13. What are wildcard characters in SQL? Explain % and _ with suitable examples.
Answer:
Wildcards are used with LIKE for pattern matching.
% – Matches zero or more characters.
Example: 'K%' matches ‘Kiran’, ‘Kunal’
_ – Matches exactly one character.
Example: 'K_nal' matches ‘Kunal’, not ‘Kiran’
14. Write SQL queries to:
a) Display all records from SALE where SAmount is greater than 200000
b) Display all records between SAmount 150000 and 250000
Answer:
a) SELECT * FROM SALE WHERE SAmount > 200000;
b) SELECT * FROM SALE WHERE SAmount BETWEEN 150000 AND 250000;
15. List any three aggregate functions in SQL with their purpose.
Answer:
1. SUM() – Returns total sum of values.
2. AVG() – Returns average of values.
3. COUNT() – Returns number of records.

16. Write a query using GROUP BY to count the number of employees in each department.
Answer:

SELECT DeptID, COUNT(*) FROM EMPLOYEE GROUP BY DeptID;

17. Explain the use of MOD() and ROUND() functions with examples.
Answer:
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

MOD(x, y) returns the remainder. Example: MOD(11, 3) returns 2

ROUND(x, y) rounds to ‘y’ decimal places. Example: ROUND(283.28, 1) returns 283.3
18. What does the NOW() function return? Also, write a query to retrieve it.
Answer:
NOW() returns the current date and time.
Query:

SELECT NOW();

19. Write the difference between single-row functions and aggregate functions. Give two examples
of each.
Answer:
Single-row functions operate on each row. Example: UCASE(), ROUND()
Aggregate functions operate on a group of rows. Example: SUM(), MAX()
20. Write queries to:
a) Display names in lowercase
b) Display first 4 characters of Cname
c) Display length of each name in EMPLOYEE
Answer:
a) SELECT LCASE(Ename) FROM EMPLOYEE;
b) SELECT MID(Cname, 1, 4) FROM CUSTOMER;
c) SELECT LENGTH(Ename) FROM EMPLOYEE;

5 MARKS QUESTIONS

QUESTION 1
Given Table: STUDENT

RollNumber SName SDateofBirth GUID

1 Atharv Ahuja 2003-05-15 444444444444

2 Daizy Bhutia 2002-02-28 111111111111
3 Taleem Shah 2002-02-28 NULL

Write SQL queries for the following:

a. Write a CREATE TABLE command to create the STUDENT table with appropriate data types.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b. Display all students whose names end with ‘Shah’.

c. Count students born after the year 2002.

d. Update the GUID of student with RollNumber 3 to 101010101010.

e. Show the name and birth year of each student.

ANSWER 1

a.

CREATE TABLE STUDENT (

RollNumber INT PRIMARY KEY,
SName VARCHAR(30),
SDateofBirth DATE,
GUID CHAR(12)
);

b.

SELECT * FROM STUDENT

WHERE SName LIKE '%Shah';

c.

SELECT COUNT(*) FROM STUDENT

WHERE YEAR(SDateofBirth) > 2002;

d.

UPDATE STUDENT
SET GUID = '101010101010'
WHERE RollNumber = 3;

e.

SELECT SName, YEAR(SDateofBirth) AS BirthYear FROM STUDENT;

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

QUESTION 2
Given Table: EMPLOYEE

EmpID Ename Salary DeptId

E001 Alok 25000 D01

E002 Bhavna 30000 D02
E003 Chetan 28000 D01

Write SQL queries for the following:

a. Write the CREATE TABLE command for EMPLOYEE with relevant types.

b. List employees working in department D01.

c. Count number of employees in each department.

d. Update the salary of ‘Chetan’ to 32000.

e. Display employees in descending order of salary.

ANSWER 2

a.

CREATE TABLE EMPLOYEE (

EmpID CHAR(5) PRIMARY KEY,
Ename VARCHAR(30),
Salary INT,
DeptId CHAR(4)
);

b.

SELECT * FROM EMPLOYEE

WHERE DeptId = 'D01';

c.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

SELECT DeptId, COUNT(*) AS NumEmployees FROM EMPLOYEE

GROUP BY DeptId;

d.

UPDATE EMPLOYEE
SET Salary = 32000
WHERE Ename = 'Chetan';

e.

SELECT * FROM EMPLOYEE

ORDER BY Salary DESC;

QUESTION 3
Given Table: GUARDIAN

GUID GName GPhone Income

111111111111 Bhutia 9812345670 420000

222222222222 Raj Ahuja NULL 310000

Write SQL queries for the following:

a. Create the GUARDIAN table with required fields.

b. Display guardian names with NULL phone numbers.

c. Count number of guardians with income above 3 lakh.

d. Update GPhone for GUID 222222222222 to ‘9012345678’.

e. Display names sorted by income descending.

ANSWER 3

a.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CREATE TABLE GUARDIAN (

GUID CHAR(12) PRIMARY KEY,
GName VARCHAR(30),
GPhone CHAR(10),
Income INT
);

b.

SELECT GName FROM GUARDIAN

WHERE GPhone IS NULL;

c.

SELECT COUNT(*) FROM GUARDIAN

WHERE Income > 300000;

d.

UPDATE GUARDIAN
SET GPhone = '9012345678'
WHERE GUID = '222222222222';

e.

SELECT GName FROM GUARDIAN

ORDER BY Income DESC;

QUESTION 4
Given Table: INVENTORY

CarId CarName Price FuelType

C01 Swift 525000.00 Petrol

C02 EECO 350000.00 CNG

Write SQL queries for the following:

a. Write the CREATE TABLE command for INVENTORY.

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b. Display all Petrol cars.

c. Count cars for each fuel type.

d. Update the price of EECO to 360000.

e. Show car names sorted by price descending.

ANSWER 4

a.

CREATE TABLE INVENTORY (

CarId CHAR(5) PRIMARY KEY,
CarName VARCHAR(30),
Price DECIMAL(10,2),
FuelType VARCHAR(10)
);

b.

SELECT * FROM INVENTORY

WHERE FuelType = 'Petrol';

c.

SELECT FuelType, COUNT(*) FROM INVENTORY

GROUP BY FuelType;

d.

UPDATE INVENTORY
SET Price = 360000.00
WHERE CarId = 'C02';

e.

SELECT CarName FROM INVENTORY

ORDER BY Price DESC;
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

QUESTION 5
Given Table: SALE

InvoiceNo CarId CustId SalePrice

I001 C01 CU01 600000.00

I002 C02 CU02 540000.00

Write SQL queries for the following:

a. Write the CREATE TABLE statement for SALE.

b. Show all sales above 550000.

c. Count number of cars sold per car ID.

d. Update SalePrice for invoice I002 to 550000.

e. Show sale records sorted by price.

ANSWER 5

a.

CREATE TABLE SALE (

InvoiceNo CHAR(5) PRIMARY KEY,
CarId CHAR(5),
CustId CHAR(5),
SalePrice DECIMAL(10,2)
);

b.

SELECT * FROM SALE

WHERE SalePrice > 550000;

c.

SELECT CarId, COUNT(*) AS TotalSold FROM SALE

GROUP BY CarId;
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

d.

UPDATE SALE
SET SalePrice = 550000.00
WHERE InvoiceNo = 'I002';

e.

SELECT * FROM SALE

ORDER BY SalePrice;

QUESTION 6
Given Table: CUSTOMER

CustId CustName CustAdd Phone Email

C001 Ananya Sen Lajpat Nagar 9811122233 ananya@[Link]

C002 Ravi Verma Rohini Sector 3 9822233344 ravi@[Link]

Write SQL queries for the following:

a. Write the CREATE TABLE command for CUSTOMER.

b. Display names of customers who live in ‘Rohini’.

c. Count customers with Gmail addresses.

d. Update the phone number of ‘Ravi Verma’ to ‘9876543210’.

e. Show all customers sorted alphabetically by name.

ANSWER 6

a.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CREATE TABLE CUSTOMER (

CustId CHAR(5) PRIMARY KEY,
CustName VARCHAR(50),
CustAdd VARCHAR(100),
Phone CHAR(10),
Email VARCHAR(50)
);

b.

SELECT CustName FROM CUSTOMER

WHERE CustAdd LIKE '%Rohini%';

c.

SELECT COUNT(*) FROM CUSTOMER

WHERE Email LIKE '%[Link]';

d.

UPDATE CUSTOMER
SET Phone = '9876543210'
WHERE CustName = 'Ravi Verma';

e.

SELECT * FROM CUSTOMER

ORDER BY CustName;

QUESTION 7
Given Table: PRODUCT

ProdID ProdName Price Stock

P01 Monitor 8000 10

P02 Keyboard 1200 30

Write SQL queries for the following:

a. Create the PRODUCT table with correct data types.

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

b. Display all products with stock more than 20.

c. Count number of products priced over 5000.

d. Update stock of ‘Monitor’ to 12.

e. Show product names sorted by price.

ANSWER 7

a.

CREATE TABLE PRODUCT (

ProdID CHAR(5) PRIMARY KEY,
ProdName VARCHAR(30),
Price INT,
Stock INT
);

b.

SELECT * FROM PRODUCT

WHERE Stock > 20;

c.

SELECT COUNT(*) FROM PRODUCT

WHERE Price > 5000;

d.

UPDATE PRODUCT
SET Stock = 12
WHERE ProdName = 'Monitor';

e.

SELECT ProdName FROM PRODUCT

ORDER BY Price;
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

QUESTION 8
Given Table: BOOK

BookID Title Author Price

B101 Python Programming Sumita Arora 450

B102 Data Structures Narayana 550

Write SQL queries for the following:

a. Write the CREATE TABLE statement for BOOK.

b. Show titles priced above 500.

c. Count books written by ‘Sumita Arora’.

d. Update price of ‘Python Programming’ to 500.

e. Display books sorted by title.

ANSWER 8

a.

CREATE TABLE BOOK (

BookID CHAR(5) PRIMARY KEY,
Title VARCHAR(50),
Author VARCHAR(50),
Price INT
);

b.

SELECT Title FROM BOOK

WHERE Price > 500;

c.

SELECT COUNT(*) FROM BOOK

WHERE Author = 'Sumita Arora';
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

d.

UPDATE BOOK
SET Price = 500
WHERE Title = 'Python Programming';

e.

SELECT * FROM BOOK

ORDER BY Title;

QUESTION 9
Given Table: SUPPLIER

SupplierID Name City

S001 Ram Traders Delhi

S002 Om Supplies Gurgaon

Write SQL queries for the following:

a. Create the SUPPLIER table.

b. Show all suppliers in Delhi.

c. Count number of suppliers in Gurgaon.

d. Update city of ‘Om Supplies’ to ‘Noida’.

e. Display supplier names in reverse alphabetical order.

ANSWER 9

a.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CREATE TABLE SUPPLIER (

SupplierID CHAR(5) PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
);

b.

SELECT * FROM SUPPLIER

WHERE City = 'Delhi';

c.

SELECT COUNT(*) FROM SUPPLIER

WHERE City = 'Gurgaon';

d.

UPDATE SUPPLIER
SET City = 'Noida'
WHERE Name = 'Om Supplies';

e.

SELECT * FROM SUPPLIER

ORDER BY Name DESC;

QUESTION 10
Given Table: ATTENDANCE

RollNo Date Status

1 2024-08-01 P
2 2024-08-01 A

Write SQL queries for the following:

a. Write the CREATE TABLE command for ATTENDANCE.

b. Show all students who were present.

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

c. Count number of absent students.

d. Update status of RollNo 2 to ‘P’.

e. List attendance sorted by RollNo.

ANSWER 10

a.

CREATE TABLE ATTENDANCE (

RollNo INT,
Date DATE,
Status CHAR(1)
);

b.

SELECT * FROM ATTENDANCE

WHERE Status = 'P';

c.

SELECT COUNT(*) FROM ATTENDANCE

WHERE Status = 'A';

d.

UPDATE ATTENDANCE
SET Status = 'P'
WHERE RollNo = 2 AND Date = '2024-08-01';

e.

SELECT * FROM ATTENDANCE

ORDER BY RollNo;

QUESTION 11
Given Table: MARKS
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

RollNo Subject Marks

1 Math 85
2 Math 78
1 Science 90

Write SQL queries for the following:

a. Create the MARKS table.

b. Find the average marks in Math.

c. Show maximum marks scored in Science.

d. Update marks of RollNo 2 in Math to 82.

e. List all marks sorted by RollNo and Subject.

ANSWER 11

a.

CREATE TABLE MARKS (

RollNo INT,
Subject VARCHAR(30),
Marks INT
);

b.

SELECT AVG(Marks) AS AvgMarks FROM MARKS

WHERE Subject = 'Math';

c.

SELECT MAX(Marks) AS MaxScience FROM MARKS

WHERE Subject = 'Science';

d.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

UPDATE MARKS
SET Marks = 82
WHERE RollNo = 2 AND Subject = 'Math';

e.

SELECT * FROM MARKS

ORDER BY RollNo, Subject;

QUESTION 12
Given Table: PRODUCT

ProdID Name Price Quantity

P01 Pen 10 100

P02 Notebook 50 80

Write SQL queries for the following:

a. Write the CREATE TABLE query for PRODUCT.

b. Calculate total stock value for each product (Price × Quantity).

c. Find the highest product price.

d. Update quantity of Pen to 120.

e. List all products sorted by name.

ANSWER 12

a.

CREATE TABLE PRODUCT (

ProdID CHAR(5) PRIMARY KEY,
Name VARCHAR(30),
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

Price INT,
Quantity INT
);

b.

SELECT Name, (Price * Quantity) AS StockValue FROM PRODUCT;

c.

SELECT MAX(Price) AS MaxPrice FROM PRODUCT;

d.

UPDATE PRODUCT
SET Quantity = 120
WHERE Name = 'Pen';

e.

SELECT * FROM PRODUCT

ORDER BY Name;

QUESTION 13
Given Table: SALES

SaleID Item Amount

S001 Pen 200

S002 Notebook 800
S003 Pen 150

Write SQL queries for the following:

a. Create the SALES table.

b. Find total sales amount for ‘Pen’.

c. Find the number of sales entries for ‘Notebook’.

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

d. Update amount of SaleID S003 to 180.

e. Display sales sorted by amount descending.

ANSWER 13

a.

CREATE TABLE SALES (

SaleID CHAR(5) PRIMARY KEY,
Item VARCHAR(30),
Amount INT
);

b.

SELECT SUM(Amount) AS TotalPenSales FROM SALES

WHERE Item = 'Pen';

c.

SELECT COUNT(*) FROM SALES

WHERE Item = 'Notebook';

d.

UPDATE SALES
SET Amount = 180
WHERE SaleID = 'S003';

e.

SELECT * FROM SALES

ORDER BY Amount DESC;

QUESTION 14
Given Table: ATTENDANCE
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

Date RollNo Status

2024-08-01 1 P
2024-08-01 2 A
2024-08-02 1 P

Write SQL queries for the following:

a. Write the CREATE TABLE statement for ATTENDANCE.

b. Count how many days RollNo 1 was present.

c. Count total records for each attendance status.

d. Update status of RollNo 2 on 2024-08-01 to ‘P’.

e. Show all records sorted by date then roll number.

ANSWER 14

a.

CREATE TABLE ATTENDANCE (

Date DATE,
RollNo INT,
Status CHAR(1)
);

b.

SELECT COUNT(*) AS DaysPresent FROM ATTENDANCE

WHERE RollNo = 1 AND Status = 'P';

c.

SELECT Status, COUNT(*) AS Count FROM ATTENDANCE

GROUP BY Status;

d.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

UPDATE ATTENDANCE
SET Status = 'P'
WHERE RollNo = 2 AND Date = '2024-08-01';

e.

SELECT * FROM ATTENDANCE

ORDER BY Date, RollNo;

QUESTION 15
Given Table: EMPLOYEE

EmpID Name Salary

E01 Aarav 45000

E02 Tanya 50000
E03 Rohan 42000

Write SQL queries for the following:

a. Create the EMPLOYEE table.

b. Find average salary.

c. Find minimum salary.

d. Update salary of ‘Rohan’ to 46000.

e. List employees sorted by salary descending.

ANSWER 15

a.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CREATE TABLE EMPLOYEE (

EmpID CHAR(5) PRIMARY KEY,
Name VARCHAR(30),
Salary INT
);

b.

SELECT AVG(Salary) AS AvgSalary FROM EMPLOYEE;

c.

SELECT MIN(Salary) AS MinSalary FROM EMPLOYEE;

d.

UPDATE EMPLOYEE
SET Salary = 46000
WHERE Name = 'Rohan';

e.

SELECT * FROM EMPLOYEE

ORDER BY Salary DESC;

QUESTION 16
Given Table: GUARDIAN

GUID GName Income

G001 Aman Kumar 420000

G002 Rekha Singh 390000
G003 D’Souza 280000

Write SQL queries for the following:

a. Write the CREATE TABLE command for GUARDIAN.

b. Find the average income of all guardians.

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

c. Count the number of guardians earning more than Rs.3 lakh.

d. Update the income of G003 to Rs.310000.

e. Display all guardians in descending order of income.

ANSWER 16

a.

CREATE TABLE GUARDIAN (

GUID CHAR(5) PRIMARY KEY,
GName VARCHAR(50),
Income INT
);

b.

SELECT AVG(Income) AS AverageIncome FROM GUARDIAN;

c.

SELECT COUNT(*) AS CountAbove3Lakh FROM GUARDIAN

WHERE Income > 300000;

d.

UPDATE GUARDIAN
SET Income = 310000
WHERE GUID = 'G003';

e.

SELECT * FROM GUARDIAN

ORDER BY Income DESC;

QUESTION 17
Given Table: LIBRARY
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

BookID Title Price

B001 DBMS Concepts 450

B002 Python Programming 500
B003 Java Basics 475

Write SQL queries for the following:

a. Write the CREATE TABLE command for LIBRARY.

b. Find the total cost of all books.

c. Display the most expensive book.

d. Update the price of ‘DBMS Concepts’ to 470.

e. Show books in ascending order of title.

ANSWER 17

a.

CREATE TABLE LIBRARY (

BookID CHAR(5) PRIMARY KEY,
Title VARCHAR(100),
Price INT
);

b.

SELECT SUM(Price) AS TotalCost FROM LIBRARY;

c.

SELECT * FROM LIBRARY

ORDER BY Price DESC
LIMIT 1;

d.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

UPDATE LIBRARY
SET Price = 470
WHERE Title = 'DBMS Concepts';

e.

SELECT * FROM LIBRARY

ORDER BY Title ASC;

QUESTION 18
Given Table: PURCHASE

PurchaseID Item Quantity Rate

P001 Pencil 100 5

P002 Eraser 50 3
P003 Sharpener 60 6

Write SQL queries for the following:

a. Create the PURCHASE table.

b. Calculate total quantity of items purchased.

c. Find maximum rate among all items.

d. Update quantity of Eraser to 70.

e. Show all purchase entries sorted by item name.

ANSWER 18

a.
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CREATE TABLE PURCHASE (

PurchaseID CHAR(5) PRIMARY KEY,
Item VARCHAR(30),
Quantity INT,
Rate INT
);

b.

SELECT SUM(Quantity) AS TotalQuantity FROM PURCHASE;

c.

SELECT MAX(Rate) AS HighestRate FROM PURCHASE;

d.

UPDATE PURCHASE
SET Quantity = 70
WHERE Item = 'Eraser';

e.

SELECT * FROM PURCHASE

ORDER BY Item;

QUESTION 19
Given Table: ORDERS

OrderID Customer Amount

O001 Aaliya 4500

O002 Tanya 5000
O003 Rohan 4200

Write SQL queries for the following:

a. Create the ORDERS table.

b. Show total order amount.

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

c. Count how many customers ordered more than Rs.4500.

d. Update amount of OrderID O003 to 4600.

e. Show all orders sorted by amount descending.

ANSWER 19

a.

CREATE TABLE ORDERS (

OrderID CHAR(5) PRIMARY KEY,
Customer VARCHAR(30),
Amount INT
);

b.

SELECT SUM(Amount) AS TotalOrders FROM ORDERS;

c.

SELECT COUNT(*) FROM ORDERS

WHERE Amount > 4500;

d.

UPDATE ORDERS
SET Amount = 4600
WHERE OrderID = 'O003';

e.

SELECT * FROM ORDERS

ORDER BY Amount DESC;

QUESTION 20
Given Table: CLASSMARKS
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

RollNo Subject Marks

101 English 88
102 English 92
103 English 80

Write SQL queries for the following:

a. Create the CLASSMARKS table.

b. Find average marks in English.

c. Find minimum marks in the subject.

d. Update marks of RollNo 103 to 85.

e. Display all marks sorted by highest to lowest.

ANSWER 20

a. CREATE TABLE CLASSMARKS (

RollNo INT,
Subject VARCHAR(30),
Marks INT);

b. SELECT AVG(Marks) AS AverageMarks FROM CLASSMARKS;

c. SELECT MIN(Marks) AS LowestMarks FROM CLASSMARKS;

d. UPDATE CLASSMARKS
SET Marks = 85
WHERE RollNo = 103;

e. SELECT * FROM CLASSMARKS

ORDER BY Marks DESC;
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

CHAPTER END EXERCISES

Exercise 1: Answer the following questions

a) RDBMS stands for Relational Database Management System.
Two RDBMS software: MySQL, Oracle

b)
- ORDER BY: Sorts query results based on one or more columns.
- GROUP BY: Groups rows with identical values for aggregate functions.
c)

Single Row Functions Aggregate Functions

Work on one row at a time Work on a group of rows

Return one result per row Return one result for entire group

d) Cartesian Product: The result of combining every row from one table with every row from another
table.
e)
- ALTER changes table structure.
- UPDATE changes table content.
- DELETE removes specific rows.
- DROP removes entire table.
f)
i) DAYNAME()
ii) MID() or SUBSTRING()
iii) MONTHNAME()
iv) UPPER() or UCASE()
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

Exercise 2: Write the output produced by the following SQL statements

a)

SELECT POW(2,3);

→8

b)

SELECT ROUND(342.9234,-1);

→ 340

c)

SELECT LENGTH("Informatics Practices");

→ 23

d)

SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"),

𝗌 MONTHNAME("1979/11/26");

→ 1979, 11, 26, November

e)

SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4),

𝗌 SUBSTR("Practices",3);

→ IND, ence, form, actices

Exercise 3: Based on MOVIE Table

Given table MOVIE with columns: MovieID, MovieName, Category, ReleaseDate, ProductionCost, Busi-
nessCost

a)

SELECT * FROM MOVIE;

b)

SELECT MovieID, MovieName, (ProductionCost + BusinessCost) AS Total_Earning FROM

𝗌 MOVIE;

c)
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

SELECT DISTINCT Category FROM MOVIE;

d)

SELECT MovieID, MovieName, (BusinessCost - ProductionCost) AS NetProfit FROM MOVIE;

e)

SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE ProductionCost > 10000 AND
𝗌 ProductionCost < 100000;

f)

SELECT * FROM MOVIE WHERE Category IN ('Comedy', 'Action');

g)

SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;

Exercise 4: Creating and Modifying Tables in SPORTS Database

a)

CREATE DATABASE Sports;

b)

CREATE TABLE TEAM (

TeamID INT CHECK(TeamID BETWEEN 1 AND 9),
TeamName VARCHAR(50) CHECK(LENGTH(TeamName) >= 10),
PRIMARY KEY (TeamID)
);

c) Already included with PRIMARY KEY on TeamID.

d)

DESC TEAM;

e)

INSERT INTO TEAM VALUES

(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

f)

SELECT * FROM TEAM;

g)

CREATE TABLE MATCH_DETAILS (

MatchID CHAR(3) PRIMARY KEY,
MatchDate DATE,
FirstTeamID INT,
SecondTeamID INT,
FirstTeamScore INT,
SecondTeamScore INT
);

Exercise 5: Queries on MATCH_DETAILS Table

a)

SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;

b)

SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;

c)

SELECT MatchID, MatchDate FROM MATCH_DETAILS WHERE FirstTeamID = 1 AND FirstTeamScore

𝗌 > SecondTeamScore;

d)

SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamID = 2 AND FirstTeamScore <

𝗌 SecondTeamScore;

e)

ALTER TABLE TEAM RENAME TO T_DATA;

ALTER TABLE T_DATA CHANGE TeamID T_ID INT;
ALTER TABLE T_DATA CHANGE TeamName T_NAME VARCHAR(50);

Exercise 6: SCHOOLUNIFORM Database Constraints

a) Insert handkerchief:
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

INSERT INTO UNIFORM VALUES (7, 'Handkerchief', 'Red');

INSERT INTO COST VALUES (7, 'M', 100);

b) Enforce referential integrity:

ALTER TABLE COST ADD CONSTRAINT FK_UCode FOREIGN KEY (UCode) REFERENCES
𝗌 UNIFORM(UCode);

c) Valid UName constraint:

ALTER TABLE UNIFORM ADD CONSTRAINT chk_UName CHECK (UName IS NOT NULL);

d) Price must be > 0:

ALTER TABLE COST ADD CONSTRAINT chk_price_gt_zero CHECK (Price > 0);

Exercise 7: Product Table Operations

a)

CREATE TABLE Product (

PCode CHAR(4) PRIMARY KEY,
PName VARCHAR(30),
UPrice INT,
Manufacturer VARCHAR(30)
);

b) Primary Key: PCode

c)

SELECT PCode, PName, UPrice FROM Product ORDER BY PName DESC, UPrice ASC;

d)

ALTER TABLE Product ADD Discount INT;

e)

UPDATE Product SET Discount = 0;

UPDATE Product SET Discount = 10 WHERE UPrice > 100;

OR
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

UPDATE Product SET Discount =

CASE
WHEN UPrice > 100 THEN UPrice * 0.10
ELSE 0
END;

f)

UPDATE Product SET UPrice = UPrice * 1.12 WHERE Manufacturer = 'Dove';

g)

SELECT Manufacturer, COUNT(*) FROM Product GROUP BY Manufacturer;

h)

SELECT PName, AVG(UPrice) FROM Product GROUP BY PName;

i)

SELECT DISTINCT Manufacturer FROM Product;

j)

SELECT COUNT(DISTINCT PName) FROM Product;

k)

SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

Exercise 8: Queries on CARSHOWROOM - INVENTORY Table

a)

ALTER TABLE INVENTORY ADD Discount INT;

b)

UPDATE INVENTORY SET Discount =

CASE
WHEN Model = 'LXI' THEN 0
WHEN Model = 'VXI' THEN Price * 0.10
ELSE Price * 0.12
END;

or
CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) II PUC

UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';

UPDATE INVENTORY SET Discount = 10 WHERE Model = 'VXI';
UPDATE INVENTORY SET Discount = 12 WHERE Model NOT IN (LXI', VXI');

c)

SELECT CarName FROM INVENTORY WHERE FuelType = 'Petrol' ORDER BY Price DESC LIMIT 1;

d)

SELECT AVG(Discount), SUM(Discount) FROM INVENTORY WHERE CarName = 'Baleno';

e)

SELECT COUNT(*) FROM INVENTORY WHERE Discount = 0;