Please refer to Computer Science For Structured Query Language SQL Class 12 Computer Science Exam Questions provided below. These questions and answers for Class 12 Computer Science have been designed based on the past trend of questions and important topics in your class 12 Computer Science books. You should go through all Class 12 Computer Science Important Questions provided by our teachers which will help you to get more marks in upcoming exams.
Class 12 Computer Science Exam Questions Structured Query Language SQL
Very Short Answer Type Questions :
Question. Which command is used to remove the table from database?
Answer: DROP TABLE
Question. Which command is used to add new record in table?
Answer: INSERT INTO
Question. In SQL, name the clause that is used to display the tuples in ascending order of an attribute
Answer: ORDER BY
Question. Which of the following is a DDL command?
a) SELECT b) ALTER c) INSERT d) UPDATE
Question. What is the minimum number of column required in MySQL to create table?
Answer: ONE (1)
Question. From the given table, Answer the following questions:
a Write the degree and cardinality of the above table.
Answer: Degree=3 and cardinality = 4
Question. Any field of above table can be made Primary key
Question. If we want to add a new column in above table which SQL command we use
Answer: Alter table House
Add <new column> <data type>;
Question. Write down name of four functions that can be used with Group by?
Answer: Count(), sum(), min(), max()
Question. The kind of join that will return all the rows from the left table in combination with the matching records or rows from the right table.
Answer: Left Join
Question. What is Group By clause?
Answer: The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It combines the multiple records in single or more columns using some functions. Generally, these functions are aggregate functions such as min(),max(),avg(), count(), and sum() to combine into single or multiple columns.
Question. Why we use Having clause?
Answer: The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Question. What is the purpose of Group By clause?
Answer: Group by clause is used in a Select statement in conjunction with aggregate functions to group the result based on distinct values in column.
Question. You have a table “Company” having column cno, cname, department and salary.
Write SQL statement to display average salary of each department.
Answer: SELECT department, avg(salary)
Group by department;
Question. Can a Group by clause be used for more than one column? If yes, given an example.
Select name, grade, class
Group by Class, grade
Question. Which of the following clause cannot be used with WHERE?
c) Group by
d) None of the above
Answer: Group By
Question. Which of the following clause can be used with Group By?
c) Order by
Answer: Having and Order By
Question. Select * from travel where order by tdate;
But he is not getting the desired result.
Help him by choosing the correct command.
a. Select * from travel order by tdate;
b. Select * from travel in ascending order;
c. Select tdate from travel order by tdate;
Answer: Select * from travel order by tdate;
Question. Which function of connection is used to check whether connection to mysql is successfully done or not?
import mysql.connector as msq
con = msq.connect( #Connection String ) # Assuming all parameter required as
passed if :
print(“ Error! Not Connected”)
Question. What is the difference in fetchall() and fetchone()?
Answer: fetchall() function is used to fetch all the records from the cursor in the form of tuple.
fetchone() is used to fetch one record at a time. Subsequent fetchone() will fetch next records. If no more records to fetch it return None.
Question. Choose the correct query to count the number of codes in each code type from travel table?
i. select count(code) from travel ;
ii. select code, count(code) from travel group by code;
iii. select code, count( distinct code) from travel;
iv. select code, count( distinct code) from travel group by code;
Choose the correct option:
a. Both (ii) and (iii)
b. Both (ii) and (iv)
c. Both (i) and (iii)
d. Only (ii)
Answer: Both (ii) and (iv)
Question. Identify the name of connector to establish bridge between Python and MySQL
Question. Write the full forms of DDL and DML. Write any two commands of DML in SQL.
Answer: DDL: Data Definition Language, DML: Data Manipulation Language
DML Commands: SELECT, UPDATE
Question. Which command is used to add new column in existing table?
Answer: ALTER TABLE
Question. Write the domain of House_Captain attribute.
Answer: Yathin, Hari Narayan, Anil Sharma, Felicita
Question. In SQL, what is the use of IS NULL operator?
Answer: IS NULL used to compare NULL values present in any column
Question. What do you understand by Degree and Cardinality of a table?
Answer: Degree: Number of attributes and Cardinality is number of tuples in a relation
Question. Which of the following types of table constraints will prevent the entry of duplicate rows? a)Unique b)Distinct c)Primary Key d)NULL
Answer: Primary Key
Question. Write a Query to insert House_Name=Tulip, House_Captain= Rajesh and
Answer: Insert into House values (‘Tulip’,’Rajesh’,278)
Question: Define – Relation, Tuple, Degree, Cardinality
Answer: A Relation is logically related data organized in the form of tables.
Tuple indicates a row in a relation.
Degree indicates the number of Columns.
Cardinality indicates the number of Columns.
Question. Write a function in SQL to find minimum and maximum value of a column in a table.
Answer: MIN ( ) and MAX ( ) aggregate functions in SQL.
Question. For the given table CLUB
What will be the degree and cardinality of table?
Answer: Degree is Number of attributes or columns which will be 7
Cardinality is Number of tuples or rows in a table which will be 10.
Question. What is the full of SQL?
Answer: Structured Query Language
Question: What is Data Dictionary?
Answer: Data Dictionary contains data about data or metadata. It represents structure or schema of a database?
Question: Name some data types in MySQL
Answer: Char, Varchar, Int, Decimal, Date, Time etc.
Question. Which of the following is used to arrange data of table?
a. ARRANGE BY
b. ORDER BY
Answer: ORDER BY
Question. In MYSQL, write the query to display list of databases store in it.
Answer: SHOW DATABASES;
Question. Consider the following query:
SELCT emp_name from Employee WHERE dept_id _______NULL;
Question: Differentiate between Char and Varchar.
Answer: Char means fixed length character data and Varchar means variable length character data. E.g. For the data “Computer” char (30) reserves constant space for 30 characters whereas Varchar (30) reserves space for only 8 characters.
Question: What is a Primary Key?
Answer: A Primary Key is a set of one or more attributes (columns) of a relation used to uniquely identify the records in it.
QQuestion: State two advantages of using Databases.
Answer: Databases help in reducing Data Duplication i.e. Data Redundancy and controls Data Inconsistency.
Question: Name some popular relational database management systems.
Answer: Oracle, MYSQL, Sqlite, MS-Access etc
Question. What is the difference between CHAR and VARCHAR datatypes in SQL?
Answer:• CHAR data type is used to store fixed-length string data and the VARCHAR data type is used to store variable-length string data.
• The storage size of the CHAR data type will always be the maximum length of this data type and the storage size of VARCHAR will be the length of the inserted string data. Hence, it is better to use the CHAR data type when the length of the string will be the same length for all the records.
• CHAR is used to store small data whereas VARCHAR is used to store large data.
• CHAR works faster and VARCHAR works slower.
Question. Which of the following is not a valid clause in SQL?
d. GROUP BY
Answer: MIN is a function in SQL (not a clause like rest of the options)
Question. Which command is used to add new row in a table?
d. None of the above
Answer: Insert command is used to add new row in a table
Question. Consider the following table Student and give output of following questions:
a SELECT * FROM Student WHERE Grade = ‘B’ ;
b SELECT MAX( Marks) FROM Student;
c SELECT Name, Marks FROM Student WHERE Subject IN (‘English’, ‘Science’);
d SELECT RollNo, Subject FROM Student WHERE Marks>= 85;
Question. Write SQL commands for the following on the basis of given table WORKER
a To display first and last name of workers having salary greater than or equal to 90000.
b To display details of workers working in HR department and Admin department
c To find average of Salary in table worker
d To Change first name of worker to ‘Satisha’ whose worker id is 007
Answer: a SELECT First_Name, Last_Name FROM Worker WHERE Salary >=90000;
b SELECT * FROM Worker WHERE Department IN (‘HR’, ‘Admin’);
c SELECT AVG(Salary) FROM worker;
d UPDATE Worker SET First_Name=’Satisha’ WHERE Worker_Id=007;
Question: What is a Foreign Key? What is its use?
Answer: A Foreign key is a non-key attribute of one relation whose values are derived from the primary key of some other relation. It is used to join two / more relations and extract data from them.
Question: Write SQL statements to do the following
Answer: (a) Create a table Result with two columns Roll and Name with Roll as primary key
CREATE TABLE Result
(Roll INT PRIMARY KEY, Name Varchar (30))
(b) Add a column Marks to Result table
ALTER TABLE Result
ADD (Marks DECIMAL (10,2))
Insert a record with values 1, “Raj”, 75.5
INSERT INTO Result
(d) Show the structure of Result table
(e) Display the records in ascending order of name
SELECT * FROM Result
ORDER BY Name
(f)Display records having marks>70
SELECT * FROM Result
WHERE Marks > 70
(g)Update marks of,”Raj” to 80
SET Marks = 80
Question: What are the various Integrity Constraints?
Answer: Various Integrity Constraints are –
NOT NULL – Ensures value for the column is not left unassigned
UNIQUE – ensures that all values in a column are distinct or no two rows can have the same values for a column having UNIQUE constraint
CHECK – Ensures that values for a particular column satisfy the specified condition
DEFAULT – Ensures that the default value is assumed if value for the column is not specified
PRIMARY KEY – Automatically applies UNIQUE and NOT NULL for uniquely identifying rows / records in a table
Short Answer Type Questions :
Question. Which is the subset of SQL commands used to manipulate database structures, including tables?
a.None of these
b.Both Data Definition Language (DDL) and Data Manipulation Language (DML)
c.Data Definition Language (DDL)
d.Data Manipulation Language (DML)
Answer: Data Definition Language (DDL)
Question. Consider the table STUDENT given below:
a. State the command that will give the output as :
i. select name from student where class=’XI’ and class=’XII’;
ii. select name from student where not class=’XI’ and class=’XII’;
iii. select name from student where city=”Agra” OR city=”Mumbai”;
iv. select name from student where city IN(“Agra”, “Mumbai”);
Choose the correct option:
a. Both (i) and (ii).
b. Both (iii) and (iv).
c. Any of the options (i), (ii) and (iv)
d. Only (iii)
Answer: Both (iii) and (iv).
Question. What will be the output of the following command?
Select * from student where gender =”F” order by marks;
c Prachi has given the following command to obtain the highest marks Select max(marks)
from student where group by class;
but she is not getting the desired result. Help her by writing the correct command.
a. Select max(marks) from student where group by class;
b. Select class, max(marks) from student group by marks;
c. Select class, max(marks) group by class from student;
d. Select class, max(marks) from student group by class;
Answer: Select class, max(marks) from student group by class;
Question. State the command to display the average marks scored by students of each gender who are in class XI?
i. Select gender, avg(marks) from student where class=“XI” group by gender;
ii Select gender, avg(marks) from student group by gender where class=”XI”;
iii. Select gender, avg(marks) group by gender from student having class=”XI”;
iv. Select gender, avg(marks) from student group by gender having class=“XI”;
Choose the correct option:
a. Both (ii) and (iii)
b. Both (ii) and (iv)
c. Both (i) and (iii)
d. Only (iv)
Answer: Only (iv)
Question. Help Ritesh to write the command to display the name of the youngest student?
a. select name,min(DOB) from student ;
b. select name,max(DOB) from student ;
c. select name,min(DOB) from student group by name ;
d. select name,maximum(DOB) from student;
Answer: c. select name,min(DOB) from student group by name ;
Question. Mr. Mittal is using a table with following columns :
Name, Class, Streamed, Stream_name
He needs to display names of students who have not been assigned any stream or have been
assigned stream_name that ends with “computers
He wrote the following command, which did not give the desired result.
SELECT Name, Class FROM Students
WHERE Stream_name = NULL OR Stream_name = “%computers” ;
Help Mr. Mittal to run the query by removing the error and write correct query
Answer: SELECT Name, Class FROM Students
WHERE Stream_name IS NULL OR Stream_name = “%computers” ;
Question. What is the use of wildcard?
Answer: LIKE is used with two wildcard characters:
a) % : used when we want to substitute multiple
characters. With % length is not fixed
b) _ (underscore) : used when we want to substitute Single character
Question. A relation Vehicles is given below :
a Display the average price of each type of vehicle having quantity more than 20.
Ans. Select avg(price), type from vehicles group by type having qty>20;
b Count the type of vehicles manufactured by each company.
Answer: Select count(type),type,company from vehicles group by company;
c Display the total price of all the types of vehicles.
Answer: Select sum(price) from vehicles group by type
Question. In a database there are two tables : Write MYSQL queries for (i) to (iii)
Table : Item
(i)To display ICode, IName and VName of all the vendors, who manufacture
(ii)To display IName, ICode, VName and price of all the products whose price >=23000
(iii)To display Vname and IName manufactured by vendor whose code is “P04”.
Answer: (i)Select ICode, IName,VName from Item I,Vendor V where I.Vcode=V.VCode and IName=’Refrigerator’
(ii) Select IName, ICode,VName from Item I,Vendor V where I.Vcode=V.VCode and Price>=23000
(iii) Select VName,IName from Item I,Vendor V where I.Vcode=V.VCode and I.VCode=’P04′
Question. Considering the Visitor table data, write the query for
(i)Write a query to display VisitorName, Coming From details of Female Visitors with
Amount Paid more than 3000
(ii)Write a query to display all coming from location uniquely
(iii)Write a query to insert the following values- 7, „Shilpa‟,‟F‟,‟Lucknow‟,3000
(iv)Write a query to display all details of visitors in order of their AmountPaid from highest to lowest
(v) Write a query to display visitor name with annual amount paid
Answer: (i) Select VisitorName,ComingFrom from Visitor
(ii) Select distinct ComingFrom from Visitor
(iii) insert into visitor values(7,’Shilpa’,’F’,’Lucknow’,3000)
(iv) Select * from visitor order by AmountPaid desc
(v) select visitorname, amountpaid*12 rom visitor;
Question: Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
(i) To display the name of all Games with their Gcodes.
(ii) To display details of those games which are having PrizeMoney more than 7000.
(iii) To display the content of the GAMES table in ascending order of ScheduleDate.
(iv) To display sum of PrizeMoney for each of the Number of participation groupings (as
shown in column Number 2 or 4).
(v) SELECT COUNT(DISTINCT Number) FROM GAMES;
(vi) SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;
(vii) SELECT SUM(PrizeMoney) FROM GAMES;
(viii) SELECT DISTINCT Gcode FROM PLAYER;
Answer: (i) SELECT GameName,Gcode FROM GAMES;
(ii) SELECT * FROM GAMES WHERE PrizeMoney>7000;
(iii) SELECT * FROM GAMES ORDER BY ScheduleDate;
(iv) SELECT SUM(PrizeMoney),Number FROM GAMES GROUP BY Number;
(vi) 19-Mar-2004 12-Dec-2003
Question: Consider the following tables FACULTY and COURSES. Write SQL commands for the statements (i) to (v) and give outputs for SQL queries (vi) to (vii).
i) To display details of those Faculties whose salary is greater than 12000.
ii) To display the details of courses whose fees is in the range of 15000 to 50000 (both values included).
iii) To increase the fees of all courses by 500 of “System Design” Course.
iv) To display details of those courses which are taught by ‘Sulekha’ in descending order of courses.
v) Select COUNT (DISTINCT F_ID) from COURSES;
vi) Select Fname, Cname from FACULTY, COURSES where COURSES.F_ID =FACULTY.F_ID;
Answer: (i) Select * from faculty where salary > 12000;
(ii) Select * from Courses.where fees between 15000 and 50000;
(iii) Update courses set fees = fees + 500 where Cname = “System Design”;
(iv) Select * from faculty fac, courses cour where fac.f_id = cour.f_id and fac.fname = ‘Sulekha’order by
Define the following terms with example:
(i) DDL (ii) DML (iii) Primary Key (iv) Candidate Key
(v) Alternate Key (vi) Foreign Key (vii) Cardinality of relation (viii) Degree of relation
(ix) Relation (x) Attribute (xi) Tuple (xii) Selection