UNIT - 1: DATABASE CONCEPTS LAST YEAR Q & Ans(part-2)

 

UNIT - 1: DATABASE CONCEPTS

LAST YEAR Q & Ans

Important Q/ ANS strictly as per the CBSE syllabus for class-XII       

INFORMATION TECHNOLOGY-802  

 

PRACTISE QUESTIONS

Q1. Which of the following is true for self-referencing table?

(a) A foreign key constraint can reference columns within the same table.

(b) A primary key constraint can be null.

(c) You cannot delete this table.

(d) You cannot update or delete this table.

 

Q2. Identify the operator that performs pattern searching in MYSQL.

(a) EXISTS operator

(b) BETWEEN operator

(c) LIKE operator

(d) SEARCH operator

 

Q3. Cardinality of a table is four and degree is three. If two columns and four rows are

added to this table, what will be the new degree and cardinality of the given table?

(a) 5,8

(b) 3,4

(c) 8,5

(d) 4,3

Q4. The Phase of application development that involves creating a database and writing code is called___________

Implementation Phase

 

Q5. While entering the data in the table, Seema is not able to leave the column age as blank in the table. What can be the possible reason?

NOT NULL constraint in age column

 

Q6. Which of the following queries will give the same output

(a) select rollno, name from student where subject in ('Information Technology','Informatics Practices');

(b) select rollno, name from student where subject between 'Information Technology' and 'Informatics

Practices';

(c) select rollno, name from student where subject ='Information Technology' or subject =' Informatics

Practices ';

(d) select rollno, name from student where subject ='Information Technology' and subject ='Informatics

Practices';

 I and iii are same

 

Q7. In the table teacher, Riya doesn't want repeated values in column marks. Rewrite the correct command.

 select marks from teacher;

select distinct marks from teacher;

 

Q8. Reema wants to input the price of shoes as 999.99. What should be the datatype and size of the column price in the table?

Decimal (5,2)

 

Q9. What is the MYSQL command to list the names of teachers in alphabetical order in the teacher table?

SELECT Name FROM Teacher ORDER BY Name;

 

Q10.Write the command to see the structure of a table?

DESCRIBE or DESC

 

Q11. The following commands are giving errors. Write the correct MYSQL commands.

 (a)alter table student delete marks;

 (b)select * from employee where name =‘%s%’;

Alter table student drop marks; select* from employee where name like ‘%s%’;

 

Q12.Ms Prabha has mistakenly entered ‘‘AMIT’’ instead of ‘‘AMITA’’ in name field of table ‘TEACHER’. Help her to write the correct SQL command to make the desired changes in the table

UPDATE Teacher SET NAME =”AMITA” WHERE NAME=”AMIT”;

 

Q13. A company is making database of its items and shipment. Item table includes item code, item name, item cost . Give details of Shipment Table along with its schema.

 

SHIPMENT (Shipment_id, Details, Shipment_date)

Name                      Type                          Remarks

Shipment_id          INT(10)                       Shipment number (Primary Key)

Details                    VARCHAR(30)            location details

Shipment_date     DATE                           Date of shipment

 

 

Q14. Write the MySQL commands for the following queries:

TRAIN

TrainID	STATION
0001	DELHI
0002	MUMBAI

 

COUNTER

TicketID	Date	Cost	TrainNo
T1	12/3/22	500	0001
T2	15/5/22	450	0002
T3	15/5/22	500	0003

 

 (a) To find all the stations, date for ticket id as T1.

(b) To find the total ticket amount collected from each station.

(c) To displays all the tables created in the current database.

(d) To delete table counter along with the information in it.

Ans:

 a. SELECT , DATE FROM TRAIN, COUNTER

WHERE TRAIN.TrainID = COUNTER.TrainNO and TicketID=”T1”;

 

b. SELECT SUM(COST), STATION FROM

TRAIN, COUNTER WHERE TRAIN.TrainID = COUNTER.TrainNO GROUP BY STATION;

 

c. SHOW TABLES;

d. DROP TABLE COUNTER;

 

Q15. Sagun is creating a table SALESMAN with fields Salesman number, Name, Total Sales. She doesn’t want the Total Sales column to be remain unfilled i.e. she wants to make entry in this field mandatory. Which constraint she should use at the time of creating SALESMAN table:

a. Check

b. Default

c. Not null

d. Primary key

Q16. Ranjana wants to delete the records where name starts with A from the table CUSTOMER having fields Name, Amount, Discount. Identify the correct command:

a. Delete CUSTOMER where name like “A%”;

b. Delete from CUSTOMER where name like “A%”;

c. Delete CUSTOMER where name = “A%”;

d. Delete from CUSTOMER where name = “A%”;

Q17. Consider a table STAFF:

Name	City
Akash	Mumbai
Ajay	Chennai
Banit	Delhi
Faujia	Mumbai
Sakal	Chennai

 

Select the command which will count the number of people in each city.

 

a. SELECT COUNT(City) , City FROM STAFF WHERE GROUP BY City;

b. SELECT COUNT(City) , City FROM STAFF GROUP BY City;

c. SELECT COUNT(City) , City FROM STAFF WHERE ORDER BY City;

d. SELECT COUNT(City) , City FROM STAFF ORDER BY City;

 

 

 

Q18. The ______________command is used to modify the structure of the table STUDENT in MySQL.

a. Modify table STUDENT

b. Alter table STUDENT

c. Alter STUDENT

d. Modify STUDENT

 

Q19. Query: To retrieve names of all the teachers having 6 characters in the First_Name and starting with 'S'. (TABLE NAME: TEACHER)

Command: -

SELECT First_Name

FROM Teacher

WHERE First_Name LIKE "S_ _ _ _ _";

  

Q20. Query: To retrieve names of all the teachers having at least 6 characters in the First_Name. (TABLE NAME: TEACHER)

Command: -

SELECT First_Name

 FROM Teacher

 WHERE First_Name LIKE "_ _ _ _ _ _%";   

 

Q21. Query: To list the names of teachers in alphabetical order and in descending order. (TABLE NAME: TEACHER)

 

Command: - FOR ALPHABETICAL ORDER

 SELECT First_Name, Last_Name

 FROM Teacher

ORDER BY First_Name, Last_Name;      

 

Command: - FOR DESCENDING ORDER

 SELECT First_Name, Last_Name

 FROM Department

ORDER BY Dept_Name DESC;        

 

Q22.Query: To find total salary of all the teachers. (TABLE NAME: TEACHER)

Command: -

SELECT SUM(Salary) AS Total_Salary FROM Teacher;               

 

Q23.Query To count the number of teachers earning more than Rs 50000. (TABLE NAME: TEACHER)

Command: -

 SELECT COUNT(Salary) FROM Teacher WHERE Salary > 50000;          

 

Q24. ________________ commands delete the tuples from a Table

i.                    Drop     ii. Delete    iii. Modify    iv. None of the above

 

DELETE FROM table_name

WHERE condition;

 

Q25. Which of the following queries will give the same output?

(a) select rollno, name from student where subject in ('Information Technology','Informatics Practices');

(b) select rollno, name from student where subject between 'Information Technology' and 'Informatics Practices';

(c) select rollno, name from student where subject ='Information Technology' and subject ='Informatics Practices';

(d) select rollno, name from student where subject ='Information Technology' or subject =' Informatics Practices ';

 

a and d                            

 

Q26. Explain Drop table with Cascade command.

DROP TABLE command with the CASCADE option is used to drop a table and all of its dependent objects.

SYNTAX: DROP TABLE customers CASCADE;

 

Q27.  Reema wants to input the price of shoes as 9999.99. What should be the datatype and size of the column price in the table?

The datatype for the price column should be decimal. The size of the column should be large enough to store the two decimal places. A suitable size for the column would be decimal (6,2).

 

Q29. Select distinct dept_name from department; What does the above query do?

a) It gives all the tuples having dept_name

b) It gives the dept_name attribute values of all tuples without repetition

c) It gives all the dept_name attribute of all the tuples

d) It gives all the dept_name attribute of all the tuples

 

Q30. Write the syntax of Select Command and its use too.

 

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 

SELECT command is a powerful tool that can be used to retrieve data from a database in a variety of ways.

 

Q31. Name the commonly used built-in aggregate functions (any four).

 

The commonly used built-in aggregate functions in SQL are:

1.       COUNT: Returns the number of rows in a table or in a specified group of rows.

2.       SUM: Returns the sum of the values in a column or in a specified group of rows.

3.       AVG: Returns the average of the values in a column or in a specified group of rows.

4.       MIN: Returns the smallest value in a column or in a specified group of rows.

5.       MAX: Returns the largest value in a column or in a specified group of rows.

 

Q32. Write any four datatypes of SQL.

1.       Character data types:  CHAR, VARCHAR

2.       Numeric data types:  INT, DECIMAL.

3.       Date and time data types:  DATE, TIME

4.       Boolean data types: TRUE and FALSE.

 

 

 

 

Q33. A HOTEL is making database of CUSTOMER and its staff. HOTEL table includes HOTELid, Hname, and Department. Give details of CUSTOMER Table along with its schema.

 

The CUSTOMER table can include the following columns:

·         CUSTOMER_ID: A unique identifier for the patient.

·         First Name: The CUSTOMER first name.

·         Last Name: The CUSTOMER last name.

·         Date of Birth: The CUSTOMER date of birth.

·         Gender: The CUSTOMER gender.

·         Address: The CUSTOMER address.

·         Phone Number: The CUSTOMER Phone number.

 

CREATE TABLE patients (

  CUSTOMER_ID INT NOT NULL,

  first_name VARCHAR(255) NOT NULL,

  last_name VARCHAR(255) NOT NULL,

  date_of_birth DATE NOT NULL,

  gender VARCHAR(255) NOT NULL,

  address VARCHAR(255) NOT NULL,

  phone_number VARCHAR(255)

);