Database Query using SQL Class 12 Important Questions

by
Share with others

Database Query using SQL is a key topic in Class 12 Computer Science and Informatics Practices. Mastering SQL helps students understand how to interact with databases using queries for inserting, retrieving, and managing data.

Contents show

Database Query using SQL Class 12 Important Questions

Q. What constraint should be applied on a table column so that duplicate values are not allowed in that column, but NULL is allowed.

Answer: UNIQUE

Q. What constraint should be applied on a table column so that NULL is not allowed in that column, but duplicate values are allowed.

Answer: NOT NULL

Q. Write an SQL command to remove the Primary Key constraint from a table, named MOBILE. M_ID is the primary key of the table.

Answer: ALTER TABLE MOBILE DROP PRIMARY KEY;

Q. Write an SQL command to make the column M_ID the Primary Key of an already existing table, named MOBILE.

Answer: ALTER TABLE MOBILE ADD PRIMARY KEY (M_ID);

Q. Consider the table ORDERS as given below

Consider the table ORDERS as given below

A) Write the following queries:

a. To display the total Quantity for each Product, excluding Products with total Quantity less than 5.

Answer:

SELECT Product, SUM(Quantity) 
FROM Orders 
GROUP BY Product 
HAVING SUM(Quantity) >= 5;

b. To display the orders table sorted by total price in descending order.

Answer:

SELECT * FROM Orders 
ORDER BY Price DESC;

c. To display the distinct customer names from the Orders table.

Answer:

SELECT DISTINCT C_Name FROM Orders;

d. Display the sum of Price of all the orders for which the quantity is null.

Answer:

SELECT SUM(Price) AS Total_Price 
FROM Orders 
WHERE Quantity IS NULL;

B) Write the output

a. Select c_name, sum(quantity) as total_quantity from orders group by c_name;

Answer:

Select c_name, sum(quantity) as total_quantity from orders group by c_name;

b. Select * from orders where product like ‘%phone%’;

Answer:

Select from orders where product like '%phone%';

c. Select o_id, c_name, product, quantity, price from orders where price between 1500 and 12000;

Answer:

Select o_id, c_name, product, quantity, price from orders where price between 1500 and 12000;

d. Select max(price) from orders;

Select max(price) from orders;

Q. Saman has been entrusted with the management of Law University Database. He needs to access some information from FACULTY and COURSES tables for a survey analysis. Help him extract the following information by writing the desired SQL queries as mentioned below.

Saman has been entrusted with the management of Law University

a. To display complete details (from both the tables) of those Faculties whose salary is less than 12000.

SELECT * 
FROM FACULTY 
NATURAL JOIN COURSES 
WHERE Salary < 12000;

b. To display the details of courses whose fees is in the range of 20000 to 50000 (both values included).

SELECT * FROM COURSES 
WHERE Fees BETWEEN 20000 AND 50000;

c. To increase the fees of all courses by 500 which have “Computer” in their Course names.

UPDATE COURSES 
SET Fees = Fees + 500 
WHERE CName LIKE '%Computer%';

d. To display names (FName and LName) of faculty taking System Design.

SELECT FName, LName 
FROM FACULTY 
NATURAL JOIN COURSES 
WHERE CName = 'System Design';

e. To display the Cartesian Product of these two tables.

SELECT * FROM FACULTY, COURSES;

Q. Ms. Shalini has just created a table named “Employee” containing columns Ename, Department and Salary. After creating the table, she realized that she has forgotten to add a primary key column in the table. Help her in writing an SQL command to add a primary key column EmpId of integer type to the table Employee. Thereafter, write the command to insert the following record in the table:

  • EmpId- 999
  • Ename- Shweta
  • Department: Production
  • Salary: 26900

Answer:

SQL Command to add primary key:

ALTER TABLE Employee ADD EmpId INTEGER NOT NULL PRIMARY KEY;

As the primary key is added as the last field, the command for inserting data will be:

INSERT INTO Employee VALUES("Shweta","Production",26900,999);

Q. Zack is working in a database named SPORT, in which he has created a table named “Sports” containing columns SportId, SportName, no_of_players, and category. After creating the table, he realized that the attribute, category has to be deleted from the table and a new attribute TypeSport of data type string has to be added. This attribute TypeSport cannot be left blank. Help Zack write the commands to complete both the tasks.

Answer:

To delete the attribute, category:

ALTER TABLE Sports DROP category;

To add the attribute, TypeSport

ALTER TABLE Sports ADD TypeSport char(10) NOT NULL;

Q. Consider the table CLUB given below and write the output of the SQL queries that follow.

Consider the table CLUB given below and write the output of the SQL queries that follow.

a. SELECT COUNT(DISTINCT SPORTS) FROM CLUB;

SELECT COUNT(DISTINCT SPORTS) FROM CLUB;

b. SELECT CNAME, SPORTS FROM CLUB WHERE DOAPP<“2006-04-30” AND CNAME LIKE “%NA”;

ELECT CNAME, SPORTS FROM CLUB WHERE DOAPP 2006-04-30 AND CNAME LIKE NA;

c. SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER = “MALE” AND PAY BETWEEN 1000 AND 1200;

SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER =

Q. Consider the table Personal given below:

Consider the table Personal given below

Based on the given table, write SQL queries for the following:

a. Increase the salary by 5% of personals whose allowance is known.

UPDATE Personal
SET Salary=Salary + Salary*0.5
WHERE Allowance IS NOT NULL;

b. Display Name and Total Salary (sum of Salary and Allowance) of all personals. The column heading ‘Total Salary’ should also be displayed.

SELECT Name, Salary + Allowance AS
"Total Salary" FROM Personal;

c. Delete the record of personals who have salary greater than 25000

DELETE FROM Personal
WHERE Salary>25000

Q. Consider the tables PRODUCT and BRAND given below:

Consider the tables PRODUCT and BRAND given below:

Write SQL queries for the following:

a. Display product name and brand name from the tables PRODUCT and BRAND.

SELECT PName, BName FROM PRODUCT P,
BRAND B WHERE P.BID=B.BID;

b. Display the structure of the table PRODUCT.

DESC PRODUCT;

c. Display the average rating of Medimix and Dove brands

SELECT BName, AVG(Rating) FROM PRODUCT
P, BRAND B
WHERE P.BID=B.BID
GROUP BY BName
HAVING BName='Medimix' OR
BName='Dove';

d. Display the name, price, and rating of products in descending order of rating.

SELECT PName, UPrice, Rating
FROM PRODUCT
 ORDER BY Rating DESC;

Q. Define the term Domain with respect to RDBMS. Give one example to support your answer.

Answer: Domain is a set of values from which an attribute can take value in each row. For example, roll no field can have only integer values and so its domain is a set of integer values

Q. Give one difference between alternate key and candidate key.

Answer: All keys that have the properties to become a primary key are candidate keys. The candidate keys that do not become primary keys are alternate keys.

Computer Science Class 12 Questions and Answers

Disclaimer: We have taken an effort to provide you with the accurate handout of “Database Query using SQL Class 12 Important Questions“. If you feel that there is any error or mistake, please contact me at anuraganand2017@gmail.com. The above CBSE study material present on our websites is for education purpose, not our copyrights. All the above content and Screenshot are taken from Computer Science Class 12 NCERT Textbook, CBSE Sample Paper, CBSE Old Sample Paper, CBSE Board Paper and CBSE Support Material which is present in CBSEACADEMIC website, NCERT websiteThis Textbook and Support Material are legally copyright by Central Board of Secondary Education. We are only providing a medium and helping the students to improve the performances in the examination. 

Images and content shown above are the property of individual organizations and are used here for reference purposes only.

For more information, refer to the official CBSE textbooks available at cbseacademic.nic.in


Share with others

Leave a Comment