SQL PRACTICE SET – (DDL & DML)

A. CREATE TABLE (10 Questions)

[Link] the SQL command to create a table STUDENT with columns RollNo (Integer,
Primary Key), Name (Varchar(30)), Class (Integer), Marks (Decimal(5,2)), City (Varchar(20))
and Gender (Char(1)).

[Link] a table EMPLOYEE with columns EmpID (Integer, Primary Key), EmpName
(Varchar(40)), Department (Varchar(20)) and Salary (Decimal(10,2)).

[Link] the SQL command to create a table BOOKS with columns BookID (Integer, Primary
Key), Title (Varchar(50)), Author (Varchar(40)), Price (Decimal(8,2)) and Publisher
(Varchar(30)).

[Link] a table COURSE with columns CCode (Char(5), Primary Key), CName
(Varchar(40)) and Duration (Integer) where Duration cannot be NULL.

[Link] the SQL command to create a table CUSTOMER having columns CID (Integer,
Primary Key), Name (Varchar(30)), City (Varchar(20)) and Age (Integer).

[Link] a table TEACHER with TID (Integer, Primary Key), TName (Varchar(30)), Subject
(Varchar(25)) and Salary (Decimal(10,2)).

[Link] a table LIBRARY with LID (Integer, Primary Key), BookName (Varchar(40)), Author
(Varchar(30)) and Price (Decimal(7,2)).

[Link] a table HOSPITAL with PID (Integer, Primary Key), PName (Varchar(30)), Disease
(Varchar(25)) and Age (Integer).

[Link] a table PRODUCT with PID (Integer, Primary Key), PName (Varchar(30)), Price
(Decimal(8,2)) and Quantity (Integer).

[Link] a table COMPANY with CID (Integer, Primary Key), CName (Varchar(40)), City
(Varchar(20)) and Employees (Integer).

B. ALTER TABLE – ADD COLUMN (5 Questions)

[Link] a new column Email of type Varchar(50) to the STUDENT table.

[Link] a column JoiningDate of type Date to the EMPLOYEE table.

[Link] a column Edition of type Integer to the BOOKS table.

[Link] a column ContactNo of type Char(10) to the CUSTOMER table.

[Link] a column Fees of type Decimal(8,2) to the COURSE table.

C. ALTER TABLE – MODIFY COLUMN (5 Questions)

[Link] the City column of STUDENT table to Varchar(30).

[Link] Salary column of EMPLOYEE table to Decimal(12,2).

[Link] Price column of BOOKS table to Decimal(10,2).

[Link] Age column of CUSTOMER table to Decimal(3,0).

[Link] Duration column of COURSE table to Varchar(10).

D. ALTER TABLE – DROP COLUMN (5 Questions)

[Link] the Email column from STUDENT table.

[Link] the JoiningDate column from EMPLOYEE table.

[Link] the Edition column from BOOKS table.

[Link] the ContactNo column from CUSTOMER table using ALTER command.

[Link] the Fees column from COURSE table.

E. DROP TABLE (5 Questions)

[Link] the SQL command to delete the STUDENT table permanently from the database.

[Link] the EMPLOYEE table from the database.

[Link] the COURSE table completely from the database.

[Link] the BOOKS table along with its structure.

[Link] the CUSTOMER table permanently from the database.

F. INSERT INTO (10 Questions)

[Link] a record into STUDENT with RollNo = 101, Name = 'Asha', Class = 12, Marks =
89.50, City = 'Delhi' and Gender = 'F'.

[Link] a record into EMPLOYEE with EmpID = 1, EmpName = 'Raj', Department = 'HR'
and Salary = 25000.

[Link] a record into BOOKS with BookID = 201, Title = 'Python Made Easy', Author = 'R.
Kumar', Price = 550.50 and Publisher = 'ABC Publications'.
[Link] a record into COURSE with CCode = 'CS01', CName = 'Computer Science' and
Duration = 12.

[Link] a record into CUSTOMER with CID = 11, Name = 'Kiran', City = 'Mumbai' and Age
= 28.

[Link] two records into STUDENT table using a single INSERT command.

[Link] a student record with RollNo = 102, Name = 'Ravi', Class = 12, Marks = NULL,
City = 'Madurai' and Gender = 'M'.

[Link] a record into EMPLOYEE without specifying column names.

[Link] a record into STUDENT specifying only Name and City values as 'Anita' and
'Chennai' respectively.

[Link] a record into BOOKS having Price = 450.75.

G. UPDATE (5 Questions)

[Link] the Marks of all students in STUDENT table by 5.

[Link] the Salary of all employees in EMPLOYEE table by 10%.

[Link] the Price of the book 'Python Made Easy' to 600.

[Link] the City of customer 'Kiran' to 'Pune'.

[Link] the Duration of the course 'Computer Science' to 15.

H. DELETE (5 Questions)
[Link] the record of the student whose RollNo is 101 from STUDENT table.

[Link] all books whose Price is less than 300 from BOOKS table.

[Link] employees whose Department is 'HR' from EMPLOYEE table.

[Link] all courses whose Duration is less than 6.

[Link] the customer record whose City is 'Pune'.

SQL PRACTICE SET –

WHERE, ORDER BY, DISTINCT, LOGICAL OPERATORS & NULL VALUES

Assume the following table structure:
STUDENT

| Field Name. | Data Type | Constraint |

| ---------- | ------------ | ----------- |
| RollNo | Integer | Primary Key |
| Name | Varchar(30) | |
| Class | Integer | |
| Marks | Decimal(5,2) | |
| City | Varchar(20) | |
| Gender | Char(1) | |

---

A. WHERE CLAUSE (10 Questions)

[Link] an SQL command to display the details of all students whose Marks are greater
than 80.

[Link] an SQL command to display the names of students who belong to the city
'Chennai'.

[Link] the details of students whose Class is equal to 12.

[Link] the details of students whose Marks are between 70 and 90.

[Link] the details of students whose City is either 'Madurai' or 'Trichy'.

[Link] the details of students whose Name starts with the letter 'A'.

[Link] the details of students whose Name ends with the letter 'a'.

[Link] the details of students whose City contains the character 'a'.

[Link] the details of students whose Marks are not NULL.

[Link] the details of students who do not belong to the city 'Chennai'.

B. ORDER BY CLAUSE (10 Questions)

[Link] all the records of STUDENT table in ascending order of Marks.

[Link] the Name and Marks of students in descending order of Marks.

[Link] all students arranged alphabetically according to Name.

[Link] all students arranged in ascending order of City.

[Link] the details of students in descending order of Class.

[Link] the Name and Marks of students in descending order of Name.

[Link] all students ordered first by City and then by Marks.

[Link] all students ordered by Class and Marks in descending order.

[Link] the details of students belonging to Chennai arranged according to Marks.

[Link] the records of students having the highest marks first.

C. DISTINCT KEYWORD (5 Questions)

[Link] all distinct city names from the STUDENT table.

[Link] all distinct Class values from the STUDENT table.

[Link] all distinct Marks obtained by students.

[Link] the number of distinct cities available in the STUDENT table.

[Link] the distinct combinations of Class and City from the STUDENT table.

D. LOGICAL OPERATORS (AND, OR, NOT) (10 Questions)

[Link] the details of students whose Marks are greater than 80 and City is 'Chennai'.

[Link] the details of students belonging to Class 12 and having Marks greater than 90.

[Link] the details of students whose City is either 'Chennai' or 'Madurai'.

[Link] the details of students whose Marks are less than 40 or greater than 90.

[Link] the details of students who do not belong to Chennai.

[Link] the details of students whose Class is not equal to 12.

[Link] the details of female students who belong to Class 12.

[Link] the details of students belonging to Chennai and having Marks greater than 75.

[Link] the details of male students whose Marks are above 80.

[Link] the details of students who belong to Madurai or Trichy and whose Marks are
above 70.
E. NULL VALUES (5 Questions)

[Link] the details of students whose Marks value is NULL.

[Link] the details of students whose Marks value is not NULL.

[Link] all records from STUDENT table where Marks value is NULL.

[Link] the Marks value to 0 for all students whose Marks value is NULL.

[Link] the number of students whose Marks value is not NULL.

F. LIKE OPERATOR (5 Questions)

[Link] the details of students whose Name starts with the letter 'R'.

[Link] the details of students whose Name ends with the letter 'n'.

[Link] the details of students whose City starts with the letter 'C'.

[Link] the details of students whose Name contains the letter 'a'.

[Link] the details of students whose City ends with the letter 'i'.

G. BETWEEN, IN AND IS NULL OPERATORS (5 Questions)

[Link] the details of students whose Marks are between 60 and 90.

[Link] the details of students whose Class is either 11 or 12.

[Link] the details of students whose City is either Chennai, Madurai or Trichy.

[Link] the details of students whose Marks value is NULL.

[Link] the details of students whose City value is not NULL.

# SQL PRACTICE SET – PART III

## SINGLE ROW FUNCTIONS, AGGREGATE FUNCTIONS, GROUP BY, HAVING &

CARTESIAN PRODUCT

### CBSE Class XII Computer Science (NCERT Pattern)

---

## Assume the following table structure:

### STUDENT

| Field Name. | Data Type | Constraint |

| ---------- | ------------ | ----------- |
| RollNo | Integer | Primary Key |
| Name Varchar(30) | |
| Class | Integer | |
| Marks | Decimal(5,2) | |
| City | Varchar(20) | |
| Gender | Char(1) | |

A. SINGLE ROW FUNCTIONS (15 Questions)

[Link] an SQL command to display the Name of students in uppercase letters using the
UPPER() function.

[Link] the City names in lowercase using the LOWER() function.

[Link] the first three characters of the City name using the SUBSTRING() function.

[Link] the length of each student's Name using the LENGTH() function.

[Link] the Marks rounded to the nearest integer using the ROUND() function.

[Link] the Marks truncated to one decimal place using the TRUNCATE() function.

[Link] the Name and current date using the CURDATE() function.

[Link] the ASCII value of the first character of each student's Name.

[Link] the Name and City together using the CONCAT() function.

[Link] the Name after replacing the letter 'a' with '@' using the REPLACE() function.

[Link] the leftmost 4 characters of Name using the LEFT() function.

[Link] the rightmost 3 characters of City using the RIGHT() function.

[Link] the absolute value of Marks using the ABS() function.

[Link] the Name and the number of characters in the Name.

[Link] the Name in uppercase and City in lowercase in the same query.

B. AGGREGATE FUNCTIONS (15 Questions)

[Link] the total number of students in the STUDENT table using COUNT().

[Link] the highest Marks obtained by a student using MAX().

[Link] the lowest Marks obtained by a student using MIN().

[Link] the average Marks of all students using AVG().

[Link] the total Marks obtained by all students using SUM().

[Link] the number of distinct cities using COUNT(DISTINCT City).

[Link] the sum of Marks obtained by students of Class 12.

[Link] the average Marks of students scoring above 80.

[Link] the difference between the highest and lowest Marks.

[Link] the count of students whose City value is not NULL.

[Link] the average Marks of female students.

[Link] the total Marks of students belonging to Chennai.

[Link] the maximum Marks obtained by male students.

[Link] the minimum Marks obtained by students of Class 11.

[Link] the number of students scoring above 90.

C. GROUP BY CLAUSE (10 Questions)

[Link] the total Marks obtained by students of each City using GROUP BY.

[Link] the average Marks obtained by students of each Class.

[Link] the number of students in each City.

[Link] the number of students in each Class.

[Link] the maximum Marks obtained in each Class.

[Link] the minimum Marks obtained in each City.

[Link] the average Marks of students according to Gender.

[Link] the total Marks obtained by male and female students separately.
[Link] the number of students belonging to each city.

[Link] the highest Marks obtained in each Gender category.

D. HAVING CLAUSE (5 Questions)

[Link] the number of students in each Class where the number of students is greater
than 2.

[Link] the total Marks of each City where the total Marks are greater than 200.

[Link] the average Marks of each Class where the average Marks are greater than 75.

[Link] the cities having more than 3 students.

[Link] the Gender groups having average Marks greater than 80.

E. CARTESIAN PRODUCT (5 Questions)

Assume another table:

SPORTS

| Field Name | Data Type |

| ---------- | ----------- |
| SportID | Integer |
| SportName | Varchar(20) |

[Link] all possible combinations of students and sports.

[Link] the Name of students and SportName using Cartesian Product.

[Link] all combinations of male students and sports.

[Link] all combinations of Class 11 students and sports.

[Link] the RollNo, Name and SportName using Cartesian Product.

F. DESCRIBE TABLE COMMANDS (5 Questions)

[Link] the SQL command to display the structure of STUDENT table.

[Link] the structure of EMPLOYEE table.

[Link] the structure of BOOKS table.

[Link] the structure of COURSE table.

[Link] the structure of CUSTOMER table.

G. MIXED BOARD-TYPE QUESTIONS (5 Questions)

[Link] the Name, City and Marks of students whose Marks are greater than the
average Marks of the class.

[Link] the City and total Marks of cities having total Marks greater than 250.

[Link] the Class and maximum Marks obtained in each Class.

[Link] the number of students in each city where the count is greater than 2.

[Link] the Name and Marks of students arranged in descending order of Marks.

Assume the following tables:

STUDENT

| Field Name | Data Type |

| ---------- | ------------ |
| RollNo | Integer |
| Name | Varchar(30) |
| Class | Integer |
| Marks | Decimal(5,2) |
| City | Varchar(20) |

RESULT

| Field Name | Data Type |

| ---------- | ----------- |
| RollNo | Integer |
| Subject | Varchar(20) |
| Grade | Char(1) |

EMPLOYEE

| Field Name | Data Type |

| ---------- | ------------- |
| EmpID | Integer |
| EmpName | Varchar(30) |
| DeptID | Integer |
| Salary | Decimal(10,2) |

DEPARTMENT

| Field Name | Data Type |

| ---------- | ----------- |
| DeptID | Integer |
| DeptName | Varchar(30) |

A. SIMPLE JOIN (10 Questions)

[Link] an SQL command to display the RollNo, Name and Grade of students by joining
STUDENT and RESULT tables.

[Link] the Name and Subject of students.

[Link] the Name, Marks and Grade of students.

[Link] the details of students who obtained Grade 'A'.

[Link] the Name and Subject of students whose Marks are greater than 80.

[Link] the Name and Grade of students belonging to Class 12.

[Link] the Name, City and Subject of students.

[Link] the Name and Grade of students from Chennai.

[Link] the details of students who scored above 90 and obtained Grade 'A'.

[Link] the Name, Subject and Grade arranged in alphabetical order of Name.

B. EMPLOYEE–DEPARTMENT JOIN (10 Questions)

[Link] EmpName and DeptName by joining EMPLOYEE and DEPARTMENT tables.

[Link] EmpName, Salary and DeptName.

[Link] the employees working in the 'Sales' department.

[Link] the employees whose salary is greater than 50000 along with their department
names.

[Link] the number of employees in each department.

[Link] the average salary of employees in each department.

[Link] the maximum salary in each department.

[Link] the minimum salary in each department.

[Link] department names having average salary greater than 40000.

[Link] EmpName and DeptName arranged in ascending order of department.

Assume:

STUDENT

| RollNo | Name | Class | City |

RESULT

| RollNo | Marks | Grade |

[Link] the Class and average Marks of students class-wise.

(JOIN + GROUP BY)

[Link] the Class and average Marks of students having average marks greater than 75.

(JOIN + GROUP BY + HAVING)

[Link] the City and maximum Marks obtained by students from each city.

(JOIN + GROUP BY)

[Link] the Grade and number of students in each grade.

(JOIN + GROUP BY)

[Link] the Class and total Marks of students whose total marks exceed 300.

(JOIN + GROUP BY + HAVING)

[Link] the names of students scoring above the average marks.

(JOIN + Aggregate)

[Link] the Grade and average Marks of students arranged in descending order.

(JOIN + GROUP BY + ORDER BY)

[Link] the Class and number of students where the number is greater than 2.

(JOIN + GROUP BY + HAVING)

[Link] the City and average Marks arranged in descending order of average marks.
(JOIN + GROUP BY + ORDER BY)

[Link] the Class and highest Marks obtained in each class.