This question asked in CBSE Outside Delhi Set, 2012

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

Question 6 Long Answer 5 - image 1.jpg

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

 

Question 6 Long Answer 5 - image 2.jpg

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’;

Question 6(ii) Long Answer 5 - image 2.jpg

(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(iii) Long Answer 5 - image 1.jpg

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.

Question 6(iv) Long Answer 5 - image 1.jpg

(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.

Question 6(v) Long Answer 5 - image 1.jpg

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.

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 13 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.