Past Year - 5 Mark Questions

Computer Science - Class 12
Chapter 9 - Structured Query Language

This question asked in CBSE Outside Delhi Set, 2012

## Consider the following tables CARDEN and CUSTOMER and answer (a), (b) and (c) parts

Table: CARDEN

 Ccode CarName Make Color Capacity Charges 501 A-Star Suzuki RED 3 14 503 Indigo Tata SILVER 3 12 502 Innova Toyota WHITE 7 15 509 SX4 Suzuki SILVER 4 14 510 C Class Mercedes RED 4 35

Table: CUSTOMER

 Code Cname Ccode 1001 Hernant Sahu 501 1002 Raj Lal 509 1003 Feroza Shah 503 1004 Ketan Dhal 502

## Illustrate Primary and Alternate Keys in the given tables (CARDEN and CUSTOMER).

• A primary key is an attribute which can uniquely identify the records in a relation/table.
• An alternate key is an attribute which has not been selected as the primary key, but is a candidate key.

In table CARDEN,

• Primary key - Ccode
• Alternate key - CarName

In table CUSTOMER,

• Primary key - Code
• Alternate key - Cname

## (i) To display the names of all the silver coloured cars.

To display the names of all the silver coloured cars, the WHERE clause followed by the condition ie., Color=’SILVER’   should be used in the query.

QUERY: SELECT CarName FROM CARDEN WHERE Color=’SILVER’;

## (ii) To display names of car, make and capacity of cars in descending order of their sitting capacity.

To display names of car, make and  capacity of cars in descending order of  their sitting capacity, the ORDER BY clause followed by the column name ie., Capacity followed by the keyword DESC should be used.

QUERY: SELECT CarName,Make,Capacity FROM CARDEN ORDER BY Capacity DESC;

## (i) SELECT COUNT(DISTINCT Make) FROM CARDEN;

The query will display the number of distinct values in the column Make.

## (ii) SELECT MAX(Charges), MIN(Charges)FROM CARDEN;

The query will display the largest and smallest value in the column Charges of table CARDEN.

Learn in your speed, with individual attention - Teachoo Maths 1-on-1 Class

### Transcript

This question asked in CBSE Outside Delhi Set, 2012 Consider the following tables CARDEN and CUSTOMER and answer (a), (b) and (c) parts Table: CARDEN Ccode CarName Make Color Capacity Charges 501 A-Star Suzuki RED 3 14 503 Indigo Tata SILVER 3 12 502 Innova Toyota WHITE 7 15 509 SX4 Suzuki SILVER 4 14 510 C Class Mercedes RED 4 35 Table: CUSTOMER Code Cname Ccode 1001 Hernant Sahu 501 1002 Raj Lal 509 1003 Feroza Shah 503 1004 Ketan Dhal 502 Question 6 (a) Illustrate Primary and Alternate Keys in the given tables (CARDEN and CUSTOMER). Answer: A primary key is an attribute which can uniquely identify the records in a relation/table. An alternate key is an attribute which has not been selected as the primary key, but is a candidate key. In table CARDEN, Primary key - Ccode Alternate key - CarName In table CUSTOMER, Primary key - Code Alternate key - Cname Question 6 (b) Write SQL commands for the following statements: (i) To display the names of all the silver coloured cars. Answer: To display the names of all the silver coloured cars, the WHERE clause followed by the condition ie., Color=’SILVER’ should be used in the query. QUERY: SELECT CarName FROM CARDEN WHERE Color=’SILVER’; (ii) To display names of car, make and capacity of cars in descending order of their sitting capacity. Answer: To display names of car, make and capacity of cars in descending order of their sitting capacity, the ORDER BY clause followed by the column name ie., Capacity followed by the keyword DESC should be used. QUERY: SELECT CarName,Make,Capacity FROM CARDEN ORDER BY Capacity DESC; Question 6 (c) Give the output of the following SQL queries: (i) SELECT COUNT(DISTINCT Make) FROM CARDEN; Answer: The query will display the number of distinct values in the column Make. (ii) SELECT MAX(Charges), MIN(Charges)FROM CARDEN; Answer: The query will display the largest and smallest value in the column Charges of table CARDEN.