Students can read the important questions given below for Structured Query Language (SQL) Class 12 Computer Science. All Structured Query Language (SQL) Class 12 Notes and questions with solutions have been prepared based on the latest syllabus and examination guidelines issued by CBSE, NCERT and KVS. You should read all notes provided by us and Class 12 Computer Science Important Questions provided for all chapters to get better marks in examinations. Computer Science Question Bank Class 12 is available on our website for free download in PDF.
Important Questions of Structured Query Language (SQL) Class 12
Short Answer Type Questions :
Question. Rakesh wants to increase the price of some of the products by 20% , of his store whose price is less than 200. Assuming the following structure , what will be the query?
Ans. UPDATE ITEM SET Price=Price + Price * 0.2 WHERE Price<200 ;
The UPDATE command updates data of a table . While updating, the expression for update value can be assigned to the updating field. The records to be updated can be specified as WHERE condition.
Question. What will be the output of the following queries on the basis of EMPLOYEE table?
(i) SELECT Salary + 100 FROM EMPLOYEE
WHERE Emp_Id = ‘E02’;
(ii) SELECT Name FROM EMPLOYEE
WHERE Emp_Id = ‘E04’;
Ans. The output of the following queries
Question. Write the queries for the following questions using the table Product with the following fields.
(P_ Code, P_Name, Qty, Price)
(i) Display the price of product having code as P06.
(ii) Display the name of all products with quantity greater than 50 and price less than 500.
Ans. (i) SELECT Price FROM Product WHERE
P_Code=“P06”;
The criteria of the records that are to be displayed can be specified with WHERE clause of SQL.
(ii) SELECT P_Name FROM Product WHERE Qty>50 AND Price<500;
The criteria of the records that are to be displayed can be specified with WHERE clause of SQL. Here, the condition is quantity > 50 and price<500 .
Question. If the value in the column is repeatable, how do you find out the unique values?
Ans. The DISTINCT clause in SQL is used to display only distinct values in a column of a table. Hence, if the column allows duplicate values the unique values can be extracted using the DISTINCT clause.
SELECT DISTINCT CLASS FROM Student ;
This displays only the unique classes.
Question. Differentiate between ALTER and UPDATE commands in SQL.
Ans. Differences between ALTER and UPDATE commands in SQL are
Question. Is it compulsory to provide values for all columns of a table while adding records? Give an example.
Ans. No it is not compulsory to provide values for all columns of a table while adding records. We can use NULL
values wherever values are missing.
e.g. INSERT INTO Employee VALUES
(1,NULL,“Sales”,89000);
Question. What is the purpose of GROUP BY clause in MySQL? How is it different from ORDER BY clause?
Ans. The GROUP BY clause can be used to combine all those records that have identical value in a particular field or a group of fields.
Whereas, ORDER BY clause is used to display the records either in ascending or descending order based on a particular field. For ascending order ASC is used and for descending order, DESC is used. The default order is ascending order.
Question. Given the command below.
DELETE FROM Toys WHERE ToyName LIKE “S_t%”;
Which records will be deleted by the above command?
Ans. The command has a LIKE clause with “S_t%” which means all the toy names that start with the letter ‘S’ and has 3rd letter as ‘t’ will deleted.
Question. Gopi Krishna is using a table Employee. It has the following columns :
Code, Name, Salary, Dept_code
He wants to display maximum salary department wise. He wrote the following command :
SELECT Deptcode, Max(Salary) FROM Employee;
But he did not get the desired result.
Rewrite the above query with necessary changes to help him get the desired output.
Ans. SELECT Deptcode, Max(Salary)
FROM Employee
GROUP BY Deptcode;
Question. In the following query how many rows will be deleted?
DELETE Student
WHERE Student_ID=109;
(Assuming a Student table with primary key Student_ID)
Ans. DELETE FROM Student WHERE Student_ID=109; Here, the “FROM” clause is missing , so the command will produce an error.
Question. How is char data type different from varchar data type?
Ans. Differences between char data type and varchar data type are
Question. Consider the following table Employee :
Write a query to get the total salary, maximum, minimum, average salary of employees (Job_ID wise), for Dept_ID 90 only.
Ans. SELECT Job_ID, SUM(Salary), AVG(Salary),
MAX(Salary), MIN(Salary)
FROM Employee
WHERE Dept_ID = ‘90’
GROUP BY Job_ID;
Question. Write the use of LIKE clause and a short explanation on the two characters used with it.
Ans. This operator is used to search a specified pattern in a column. It is useful when you want to search rows to match a specific pattern or when you do not know the entire value. The SQL LIKE clause is used to compare a value to similar values using wildcard characters.
We describe patterns by using two special wildcard characters, given below:
(i) The per cent sign (%) is used to match any substring.
(ii) The underscore (_) is used to match any single character.
The symbols can also be used in combinations.
Question. Write syntax of the conditions given below.
(i) Add a column in a table.
(ii) Delete a column from a table.
Ans. (i) ALTER TABLE<table_name>ADD
<column_name>datatype<value>;
(ii) ALTER TABLE<table_name>DROP
COLUMN<column_name>;
Question. Amit wrote the command to create a table “Student” as :
CREATE TABLE Student(RollNo integer, Name varchar(20), Marks float(8,2));
What does (8,2) mean here?
Ans. While specifying float columns in a table the width and the number of decimals have to be specified. Here 8 is the total width and 2 is the number of decimal places for the Marks column.
Question. What do you mean by an operator? Name any four operators used in queries.
Ans. An operator is a component of an expression that represents the action that should be taken over a set of values.
Four operators used in queries are
(i) Arithmetic operators
(ii) Comparison operators
(iii) Boolean/Logical operators
(iv) Between operator
Question. How NOT operator is used with WHERE clause?
Give an example.
Ans. The WHERE clause is used to retrieve some given data according to the condition and NOT operator reverses the result of it.
For example,
mysql>SELECT Name, Class, Games FROM
Student_table WHERE NOT Games = ‘FootBALL’;
Question. What are the aggregate functions in SQL?
Ans. Aggregate function is a function where the values of multiple-rows are grouped together as input on certain criteria to form a single value of more significant meaning. Some aggregate functions used in SQL are SUM ( ), AVG( ), MIN(), etc.
Question. Explain the use of ORDER BY clause.
Ans. The ORDER BY clause is used to arrange the records in ascending or descending order. Data present in a table can be arranged as per requirement on a specific field in ascending or descending order. The default is ascending order. To arrange in descending order the DESC clause is to be used. To arrange in ascending order ASC may be used.
e.g. SELECT * FROM Employee ORDER BY EMP_SALARY DESC;
The above command arranges the records in descending order of salary.
Question. What are the functions of ALTER TABLE command?
Ans. The main functions of ALTER TABLE command are
(i) Add or drop columns.
(ii) Change the column definition of a column.
(iii) Add or drop constraint.
(iv) Rename a column.
Question. Consider the following table PREPAID. Write MySQL commands for the statements given below.
(i) DELETE a column name Model.
(ii) DELETE a customer record where connection type is BSNL.
Ans. (i) mysql> ALTER TABLE PREPAID DROP Model;
(ii) mysql> DELETE FROM PREPAID WHERE Connection = ‘BSNL’;
Long Answer Type Questions :
Question. Consider the following tables. Write SQL commands for the statements (i) to (v).
(i) To display the names of all Senders from Mumbai.
(ii) To display the RecID, SenderName, SenderAddress, RecName, RecAddress for every Recipient.
(iii) To display Recipient details in ascending order of RecName.
(iv) To display number of Recipients from each City.
(v) To display the detail of recipients who are in Mumbai.
Ans. (i) SELECT SenderName FROM SENDER WHERE SenderCity = ‘Mumbai’;
(ii) SELECT RecID, SenderName, SenderAddress, RecName, RecAddress
FROM RECIPIENT, SENDER WHERE
RECIPIENT.SenderID = SENDER.SenderID;
(iii) SELECT * FROM RECIPIENT ORDER BY RecName;
(iv) SELECT COUNT(*) AS “No. of Recipients”,
RecCity FROM RECIPIENT
GROUP BY RecCity;
(v) SELECT * FROM RECIPIENT
WHERE RecCity = “Mumbai”;
Question. Write SQL commands for (i) to (iv) on the basis of table COLLEGE
(i) Write a query to change the Basic salary to 10500 of all those teachers from COLLEGE, who joined the COLLEGE after 01/02/89 and are above the age of 50.
(ii) Write a query to display Name, Age and Basic of all those from COLLEGE, who belong to Physics and Chemistry department only.
(iii) Which command will be used to delete a row from table COLLEGE, in which NAME is VIREN?
(iv) Insert the following data in the given table COLLEGE
11, ‘Saurav’, 50, ‘Chemistry’, ‘18/05/93’, 12000, ‘M’
Ans. (i) UPDATE COLLEGE SET Basic = 10500 WHERE DateofJoin>‘01/02/89’ AND Age>50;
(ii) SELECT Name, Age, Basic FROM COLLEGE WHERE Department=‘Physics’ OR Department=‘Chemistry’;
(iii) DELETE FROM COLLEGE WHERE Name = ‘VIREN’;
(iv) INSERT INTO COLLEGE VALUES (11, ‘Saurav’, 50, ‘Chemistry’, ‘18/05/93’,
12000, ‘M’);
Question. Answer the questions (i) to (v) on the basis of the following tables SHOPPE and ACCESSORIES.
(i) To display Name and Price of all the Accessories in ascending order of their Price.
(ii) To display Id and SName of all Shoppe located in Nehru Place.
(iii) To display Minimum and Maximum Price of each Name of Accessories.
(iv) To display Name, Price of all Accessories and their respective SName, where they are available.
(v) To display name of accessories whose price is greater than 1000.
Ans. (i) SELECT Name, Price
FROM ACCESSORIES
ORDER BY Price;
(ii) SELECT Id, SName
FROM SHOPPE
WHERE Area = ‘Nehru Place’;
(iii) SELECT MIN(Price) “Minimum Price”,
MAX(Price) “Maximum Price”, Name
FROM ACCESSORIES
GROUP BY Name;
(iv) SELECT Name, Price, SName
FROM ACCESSORIES A, SHOPPE S
WHERE A.Id = S.Id;
but this query enable to show the result because
A.Id and S.Id are not identical.
(v) SELECT Name From
ACCESSORIES
WHERE Price>1000;
Question. Write SQL commands for (i) to (v) on the basis of table INTERIORS.
(i) To show all information about the Sofa from the INTERIORS table.
(ii) To list the ITEMNAME, which are priced at more than 10000 from the INTERIORS table.
(iii) To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02 from the INTERIORS table in descending order of ITEMNAME.
(iv) To insert a new row in the INTERIORS table with the following data
{14, ‘TrueIndian’, ‘Office Table’,
‘25/03/03’, 15000, 20}
(v) To display the name of item with their price which have discount more than 20.
Ans. (i) SELECT * FROM INTERIORS WHERE TYPE = ‘Sofa’;
(ii) SELECT ITEMNAME FROM INTERIORS WHERE PRICE > 10000;
(iii) SELECT ITEMNAME, TYPE FROM INTERIORS WHERE DATEOFSTOCK < ‘22/01/02’
ORDER BY ITEMNAME DESC;
(iv) INSERT INTO INTERIORS VALUES (14,‘TrueIndian’, ‘Office Table’,
‘25/03/03’,15000,20);
(v) SELECT ITEMNAM, PRICE FROM INTERIORS WHRE DISCOUNT>20;
Question. Consider the following tables STUDENT and STREAM. Write SQL commands for the statements (i) to (v).
(i) To display the name of streams in alphabetical order from table STREAM.
(ii) To display the number of students whose POINTS are more than 5.
(iii) To update GRADE to ‘A’ for all those students, who are getting more than 8 as POINTS.
(iv) ARTS+MATHS stream is no more available. Make necessary change in table STREAM.
(v) To display student’s name whose stream name is science and computer.
Ans. (i) SELECT STRNAME FROM STREAM ORDER BY STRNAME;
(ii) SELECT COUNT(*) FROM STUDENT WHERE POINTS > 5;
(iii) UPDATE STUDENT SET GRADE = ‘A’ WHERE POINTS > 8;
(iv) DELETE FROM STREAM WHERE STRNAME = ‘ARTS + MATHS’;
(v) SELECT NAME FROM STUDENT
WHERE STUDENT.STRCDE = STREAM.STRCDE
AND STRNAME = “SCIENCE + COMP”;
Question. Study the following tables DOCTOR and SALARY and write SQL commands for the questions (i) to (v).
(i) Display NAME of all doctors who are in MEDICINE department having more than 10yrs experience from the table DOCTOR.
(ii) Display the average salary of all doctors working in ENT department using the tables DOCTOR and SALARY. SALARY = BASIC + ALLOWANCE.
(iii) Display the minimum ALLOWANCE of female doctors.
(iv) Display the highest consultation fee among all male doctors.
(v) To display the detail of doctor who have experience more than 12 years.
Ans. (i) SELECT NAME FROM DOCTOR WHERE DEPT = ‘MEDICINE’ AND EXPERIENCE > 10;
(ii) SELECT AVG(BASIC + ALLOWANCE) FROM SALARY WHERE SALARY.ID IN(SELECT ID FROM DOCTOR WHERE DEPT = ‘ENT’);
(iii) SELECT MIN(ALLOWANCE) FROM SALARY WHERE SALARY.ID IN(SELECT ID FROM DOCTOR WHERE SEX = ‘F’);
(iv) SELECT MAX(CONSULTATION) FROM SALARY WHERE SALARY.ID IN(SELECT ID FROM DOCTOR WHERE SEX = ‘M’);
(v) SELECT * FROM DOCTOR
WHERE EXPERIENCE>12;
Question. Write the SQL commands for (i) to (v) on the basis of the table HOSPITAL.
(i) To show all information about the patients of Cardiology Department.
(ii) To list the name of female patients, who are in Orthopaedic Department.
(iii) To list names of all patients with their date of admission in ascending order.
(iv) To display Patient’s Name, Charges, Age for male patients only.
(v) To display name of doctor are older than 30 years and charges for consultation fee is more than 500.
Ans. (i) SELECT * FROM HOSPITAL WHERE Department = ‘Cardiology’;
(ii) SELECT Name FROM HOSPITAL WHERE Department = ‘Orthopaedic’ AND Sex = ‘F’;
(iii) SELECT Name FROM HOSPITAL ORDER BY Dateofadm;
(iv) SELECT Name, Charges, Age FROM HOSPITAL WHERE Sex = ‘M’;
(v) SELECT NAME FROM HOSPITAL WHERE Age>30 AND Charges>500;
Question. Given the following tables for a database LIBRARY.
Write SQL queries for (i) to (v).
(i) To show Book name, Author name and Price of books of EPB Publishers.
(ii) To list the names from books of Fiction type.
(iii) To display the names and price of the books in descending order of their price.
(iv) To increase the price of all books of First Publ Publishers by 50.
(v) To display the detail of book whose quantity less than 10.
Ans. (i) SELECT Book_Name, Author_Name, Price FROM BOOKS WHERE Publishers = ‘EPB’;
(ii) SELECT Book_Name FROM BOOKS WHERE Type = ‘Fiction’;
(iii) SELECT Book_Name, Price FROM BOOKS ORDER BY Price DESC;
(iv) UPDATE BOOKS SET Price = Price + 50 WHERE Publishers = ‘First Publ’;
(v) SELECT *FROM BOOKS WHERE Qty<10;
Question. Study the following tables FLIGHTS and FARES and write SQL commands for the questions (i) to (iv).
(i) Display FL_NO and NO_FLIGHT from KANPUR to BENGALURU from the table FLIGHTS.
(ii) Arrange the contents of the table FLIGHTS in the ascending order of FL_NO.
(iii) Display the FL_NO and fare to be paid for the flights from DELHI to MUMBAI using the tables FLIGHTS and FARES, where the fare to be paid = FARE + FARE * TAX % 100.
(iv) Display the minimum fare INDIAN AIRLINES is offering from the table FARES.
(v) To display the detail fares of Indian airlines.
Ans. (i) SELECT FL_NO, NO_FLIGHT FROM FLIGHTS WHERE STARTING = ‘KANPUR’ AND ENDING = ‘BENGALURU’;
(ii) SELECT * FROM FLIGHTS ORDER BY FL_NO;
(iii) SELECT FL_NO, FARE + FARE * TAX%100 FROM FARES WHERE FL_NO = (SELECT FL_No FROM FLIGHTS WHERE STARTING = ‘DELHI’ AND ENDING = ‘MUMBAI’);
(iv) SELECT MIN(FARE) FROM FARES GROUP BY AIRLINES HAVING AIRLINES = ‘INDIAN AIRLINES’;
(v) SELECT * FROM FARES
WHERE AIRLINES = “Indian Airlines”;
Question. Write SQL commands for (i) to (v) on the basis of table STUDENT
(i) List the name of all the students, who have taken stream as COMPUTER.
(ii) To count the number of female students.
(iii) To display the number of students stream wise.
(iv) To display all the records in sorted order of name.
(v) To display the stream of student whose name is Harish.
Ans. (i) SELECT NAME FROM STUDENT WHERE STREAM =‘COMPUTER’;
(ii) SELECT COUNT(*) FROM STUDENT WHERE SEX = ‘F’;
(iii) SELECT STREAM, COUNT(*) FROM STUDENT GROUP BY STREAM;
(iv) SELECT * FROM STUDENT ORDER BY NAME;
(v) SELECT STREAM FROM STUDENT
WHERE NAME LIKE “%HARISH%”;
Question. Consider the following tables STORE and answer the questions:
Write SQL commands for the following statements:
(i) To display details of all the items in the STORE table in ascending order of LastBuy.
(ii) To display ItemNo and Item name of those items from STORE table, whose Rate is more than ` 15.
(iii) To display the details of those items whose Supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table STORE.
(iv) To display minimum rate of items for each Supplier individually as per Scode from the table STORE.
(v) To display the item with its quantity which include pen in their name.
Ans. (i) SELECT * FROM STORE ORDER BY LastBuy;
(ii) SELECT ItemNo, Item FROM STORE WHERE Rate>15;
(iii) SELECT * FROM STORE WHERE Scode = 22 OR Qty>110;
(iv) SELECT MIN(Rate) FROM STORE GROUP BY Scode;
(v) SELECT Item, Qty FROM STORE WHERE Item LIKE ‘%Pen%’;
Question. Write SQL commands for (i) to (iv) on the basis of the table SPORTS
(i) Display the games taken up by the students, whose name starts with ‘A’.
(ii) Write a query to add a new column named MARKS.
(iii) Write a query to assign a value 200 for Marks for all those, who are getting grade ‘B’ or grade ‘A’ in both GAME1 and GAME2.
(iv) Which command will be used to arrange the whole table in the alphabetical order of NAME?
(a) SELECT FROM SPORTS ORDER BY NAME;
(b) SELECT*SPORTS ORDER BY NAME;
(c) SELECT*FROM SPORTS ORDER NAME;
(d) SELECT*FROM SPORTS ORDER BY NAME;
Ans. (i) SELECT GAME1, GAME2 FROM SPORTS WHERE NAME LIKE ‘A%’;
(ii) ALTER TABLE SPORTS ADD(MARKS NUMBER(3));
(iii) UPDATE SPORTS SET MARKS = 200
WHERE GRADE = ‘A’ OR GRADE = ‘B’ OR GRADE1 = ‘A’ OR GRADE1 = ‘B’;
(iv) (d) SELECT * FROM SPORTS ORDER BY NAME;
Question. Given the following family relation. Write SQL commands for questions (i) to (v) based on the table
(i) To select all the information of family, whose Occupation is Service.
(ii) To list the name of family, where female members are more than 3.
(iii) To list all names of family with income in ascending order.
(iv) To count the number of family, whose income is less than 10000.
(v) To display the detail of family whose income is more than 10000 and occupation is mixed type.
Ans. (i) SELECT * FROM FAMILY WHERE Occupation = ‘Service’;
(ii) SELECT Name FROM FAMILY WHERE FemaleMembers > 3;
(iii) SELECT Name, Income FROM FAMILY ORDER BY Income;
(iv) SELECT COUNT(*) FROM FAMILY WHERE Income < 10000;
(v) SELECT *FROM FAMILY
WHERE INCOME > 10000
AND Occupation = “Mixed”;
Question. What is the differences between HAVING clause and WHERE clause?
Ans. Differences between HAVING clause and WHERE clause are
Question. Write SQL commands for (i) to (iv) on the basis of table EMPLOYEE
(i) Which command will be used to list the names of the employees, who are more than 34 years old sorted by NAME.
(a) SELECT NAME FROM EMPLOYEE WHERE AGE>34 ORDER BY NAME;
(b) SELECT * FROM EMPLOYEE WHERE AGE>34 ORDER BY NAME;
(c) SELECT NAME FROM EMPLOYEE WHERE AGE>34;
(d) SELECT NAME FROM EMPLOYEE AGE>34 ORDER BY NAME;
(ii) Write a query to display a report, listing NAME, BASIC, DEPARTMENT and annual salary.
Annual salary equals to BASIC * 12.
(iii) Insert the following data in the EMPLOYEE table
11, ‘VIJAY’, 9300, ‘FINANCE’, ‘13/7/98’, 35, “M”
(iv)Write a query to count the number of employees, who are either working in PERSONNEL or COMPUTER department.
Ans. (i) (a) SELECT NAME FROM EMPLOYEE WHERE AGE > 34 ORDER BY NAME;
(ii) SELECT NAME, BASIC, DEPARTMENT, BASIC*12 “Annual Salary” FROM EMPLOYEE;
(iii) INSERT INTO EMPLOYEE VALUES(11, ‘VIJAY’, 9300, ‘FINANCE’, ‘13/7/98’, 35,‘M’);
(iv) SELECT COUNT(*) FROM EMPLOYEE
WHERE DEPARTMENT = ‘PERSONNEL’ OR DEPARTMENT = ‘COMPUTER’;
Question. Consider the following tables GARMENT and FABRIC. Write SQL commands for the statements (i) to (v).
(i) To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE.
(ii) To display the details of all the GARMENTs, which have READYDATE in between 08-DEC-07 and 16-JUN-08 (inclusive of both the dates).
(iii) To display the average PRICE of all the GARMENTs. Which are made up of FABRIC with FCODE as F03.
(iv) To display FABRIC wise highest and lowest price of GARMENTs from GARMENT table. (Display FCODE of each GARMENT alongwith highest and lowest price.)
(v) To display garment’s description with their price whose fabric is silk.
Ans. (i) SELECT GCODE, DESCRIPTION FROM GARMENT ORDER BY GCODE DESC;
(ii) SELECT*FROM GARMENT
WHERE READYDATE BETWEEN ‘08-DEC-07’ AND ‘16-JUN-08’;
(iii) SELECT AVG(PRICE) FROM GARMENT WHERE FCODE = ‘F03’;
(iv) SELECT FCODE, MAX(PRICE), MIN(PRICE) FROM GARMENT GROUP BY FCODE;
(v) SELECT DESCRIPTION, PRICE FROM GARMENT WHERE GARMENT.FCODE = FABRIC.FCODE AND TYPE = “SILK”;
Question. Consider the following tables PRODUCT and CLIENT. Write SQL commands for the statement (i) to (v).
(i) To display the details of those Clients, whose City is Delhi.
(ii) To display the details of products, whose Price is in the range of 50 to 100 (both values included).
(iii) To display the ClientName, City from table CLIENT and ProductName and Price from table PRODUCT, with their corresponding matching P_ID.
(iv) To increase the Price of all products by 10.
(v) To display the product detail whose manufacturer is ABC and price less than 50.
Ans. (i) SELECT * FROM CLIENT WHERE City=‘Delhi’;
(ii) SELECT * FROM PRODUCT WHERE Price BETWEEN 50 AND 100;
(iii) SELECT ClientName, City, ProductName, Price FROM CLIENT, PRODUCT
WHERE CLIENT.P_ID = PRODUCT.P_ID;
(iv) UPDATE PRODUCT SET Price = Price + 10;
(v) SELECT *FROM PRODUCT
WEHRE Manufacturer = “ABC”
AND Price < 50;
