CBSE Class 11 Information Practices Data Manipulation Commands Worksheet

Table Creation And Data Manipulation Commands

Q1. Consider the following table named “GYM” with details about Fitness products being sold in the store :

Write SQL statements to do the following :

a) Display the names of all the products in the store.

b) Display the names and unit price of all the products in the store.

c) Display the names of all the products with unit price less than Rs 200000.00

d) Display details of all the products with unit price in the range 20000 to 30000

e) Display name of all products by the manufacturer “Fit Express”.

f) Display all rows sorted in descending order of unit price.

g) Add a new row for product with the details : “P106”, “Vibro Exercise”, 23000, “Avon Fitness”.

h) Change the unit price data of all the rows by applying 10% discount reduction on all the products.

i) Display details of all products with manufacturer name starting with “A”.

* Prcode : Stores codes of Products. ** Prname : Stores names of Products *** Unit price is in Rs.

Table Creation And Data Manipulation Commands

Q1. Consider the following table “Employee”.

• Ecode should be declared as Primary key, Unique.

• Salary must be greater than 1000.

• First name and Last name must not remain blank.

• Make data type for “Salary” with 8 digits maximum with 3 digits decimals.

Write SQL statements to do the following :

a) Combine and display the first names and last names of all employees with Example : “Amit Sharma”

b) Display the department names , without duplicates.

c) Display all the details of employees with last names as “Lakshmi”.

d) Display all the details of employees whose last name is “Rajlani” or “Sharma”.

e) Display the codes and first names of all employees of ‘Accounts’ department.

f) Display department names of departments with salary above 18000.

g) Display First name and first letter of Lastname (Exp :Amit S.)

h) Combine and display Second name and First name where department is accounts.

i) Increase 50 OR of all the employees where salary is less than 2000.

j) Delete the records where salary is more than 5000.

k) Display all the first name, salary (round to 0 decimal place).

l) Rename the field Department to Dept.

m)Delete the column Last name.

n) Delete all the records

o) Delete the table.

Q.1 If you have to create a table CUSTOMER. Identify the required data types for each attributes : Cust_ID Customer Identification Number, Cust_Name Customer Name,Cust_Add, Customer Address, Bill_No Customer bill Number

Q.2 Create a table name as “Deptm” with the following structure :

(b) Write SQL Commands for the questions form (a) to (h) on the basis of table Teacher.

Table :Teacher

a) To show all information about the teacher of History department in descending order of their name .

b) To list the male teacher who are in Maths department.

c) To display Name, Salary, where age less than 25 and age equal to or more than 45.

d) Update the Salary by increasing Rs. 1000 for teachers with age more than 55.

e) To Insert a new record in table Teacher with the following data :

‘Raja’, 23, ‘Hindi’, ‘2005-08-19’,12675.

f) Write a SQL command to add following column in above table.

Column Name Data Type Size Constraint Description

Address Varchar 40 Address of the Person

g) Display the name of those teacher whose name started with alphabet ‘S’;

h) To Delete those records where Department is History.

i) Write SQL Command to drop the table Teacher.

2. Find the Output of following :

(i) SELECT ROUND (1.298,1);

(ii) SELECT POW(2,4);

(iii) SELECT LOWER(‘MYSQL QUERY LANGUAGE’);

(iv) SELECT SUBSTR(‘MYSQL LANGUAGE’, 7,8);

(v) SELECT LENGTH(‘INFORMATION’); 

Table Creation And Data Manipulation Commands

Q1. Create table One with the following structure :

Q2. Write SQL command to display the table structure.

Q3. Add the following data in the above table(ONE) :

Q4. Add 5 marks for all the students.

Q5. Add 2 marks where rollno greater than 3.

Q6. Change the field name name to Sname.

Q7. Modify the Add char(20) to Char (30).

Q.8. Change the width of Grno to number(4).

Q9. Modify the grno position to first position in the table.

Q10. Update the field grno as Primary Key.

Q11.Delete the record(s) where mark is less than 50.

Q12. Remove the entire contents of the table.

Q13. Drop the table “ONE”. 

Table Creation And Data Manipulation Commands

Q1. Consider the following table named “GYM” with details about Fitness products being sold in the store :

Write SQL statements to do the following :

a) Display the names of all the products in the store.

b) Display the names and unit price of all the products in the store.

c) Display the names of all the products with unit price less than Rs 200000.00

d) Display details of all the products with unit price in the range 20000 to 30000

e) Display name of all products by the manufacturer “Fit Express”.

f) Display all rows sorted in descending order of unit price.

g) Add a new row for product with the details : “P106”, “Vibro Exercise”, 23000, “Avon Fitness”.

h) Change the unit price data of all the rows by applying 10% discount reduction on all the products.

i) Display details of all products with manufacturer name starting with “A”.

* Prcode : Stores codes of Products. ** Prname : Stores names of Products *** Unit price is in Rs.