Class 11 Worksheet
1. Create the following table STOCK with suitable data types and constraints for columns. Add the
records and do the following:
Table: STOCK
a. To display the details of all the Items in the Stock table in ascending order of StockDate.
b. To display the details of those Items in Stock table whose Dealer Code (Dcode) is either 102 or
quantity (Qty) greater than 100.
c. To display ItemNo, ItemName and New Unit price as UnitPrice + 5 with proper heading.
d. To display the ItemNo , ItemName and Qty of the stock that have ‘Pen’ in their ItemName .
e. To remove the items from the table stock where ItemNo is ‘S006’
2. Rajeev created a table named PRODUCT. He wants to see those Products whose price is between 400 and
1200. He wrote a query.
Select * from PRODUCT whose Price > 400 or < 1200;
Help Rajeev to run the query by removing the errors and rewriting it.
3. Rewrite the following statements after removing the errors. Underline the correction done
i. SELECT Commission FROM Sales WHERE Commission = NULL;
ii. SELECT Commission FROM Sales WHERE Commission! = NULL;
4. The column Sname of the table STUDENT is given below:
What will be the output of the following queries?
i. SELECT Sname FROM STUDENT WHERE Sname like “%a”;
ii. SELECT Sname FROM STUDENT WHERE Sname like “_r%”;
5. Identify which two SQL queries will produce the same output:
a. SELECT * FROM student WHERE Marks >=80 AND marks <= 90;
b. SELECT * FROM Student WHERE Marks >=80 OR Marks <=90;
c. SELECT * FROM student WHERE Marks BETWEEN 80 AND 90;
d. SELECT * FROM Student WHERE Marks >80 AND Marks < 90;
6 Create a table Hotel with the following structure:
a. Change the data type of Type column to VARCHAR(20).
b. Change the name of Type column to RoomType.
c. Add the primary key constraint to RoomNo.
d. Add the column Remarks of VARCHAR(20).
e. Add 5 records.
7 Create the table IdentityCards containing information of the people of an organization with the
following data. Write SQL command for the statements (a) to (g).
a. Display records in descending order of Data of Birth.
b. Display all the records of Female.
c. Display the number of Female and Male records.
d. Display the details of records having name starting with ‘A’.
e. Display the details of card number C003 and C006.
f. Change the BloodGroup of CardNo C003 to B+
8. Create the table WORKER and add records as shown below. Write SQL commands for the statements
a. Display EMPNO and ENAME of all employees from table Worker.
b. Display employee name, salary and department number who are not getting commission from
table Worker.
c. Display employee number, name, sal and sal * 12 as Annual Salary from table Worker with
heading ‘Annual Salary’.
d. List all distinct department number in table Worker.
e. List all records whose salary is between 20000 and 50000 from table Worker.
f. List the details of all employees whose name starts with ‘M’.
g. List the details of all employees who are either CLERK or SALESMAN.
h. List the details of all employees who were hired in year 1991.
9. Create a table EMPLOYEE with the following data. Write SQL commands for the statements
Table: EMPLOYEE
a. To display the list of all employees whose salary is between 30000 and 40000.
b. To display the employee names in descending order of age.
c. To display the employee list where grade is either ‘A’ or ‘B’.
d. To display the list of employees where grade is not ‘A’.
e. To display the grade and number of employees in each grade.
f. Delete the Column “Area” from the table.
g. Add a new column “Phone” of character type with size 50.
10. 17. Given the following table (Student) :
Answer the following questions
Write MYSQL commands for (i) to (vi)
(i) Write the data types used in above table.
(ii) To list the names and department of female students who are in History
department.
(iii)To list name of all students with their date of admission in ascending order.
(iv) To change the value of Fee to 120 whose roll no is 5.
(v) To display all records where name ends with character 'Y'.
(vi) To delete the column age from the table Student.