Scribd
Upload
5 views3 pages

SQL Worksheet

The document contains SQL commands and queries related to a database for a car and stock management system. It includes examples of selecting data, aggregating results, and handling errors in SQL syntax. Additionally, it discusses candidate keys and provides outputs for various SQL queries performed on the CARDEN and STOCK tables.

Uploaded by

Sundar M
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views3 pages

SQL Worksheet

The document contains SQL commands and queries related to a database for a car and stock management system. It includes examples of selecting data, aggregating results, and handling errors in SQL syntax. Additionally, it discusses candidate keys and provides outputs for various SQL queries performed on the CARDEN and STOCK tables.

Uploaded by

Sundar M
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MAHARISHI VIDYA MANDIR SENIOR SECONDARY SCHOOL, TVT, CH-19

SQL WORKSHEET WITH ANSWERS


a) SQL Commands
1. Silver coloured cars
SELECT CarName FROM CARDEN
WHERE Colour = 'SILVER';
2. Car name, Make, Capacity in descending order
SELECT CarName, Make, Capacity
FROM CARDEN
ORDER BY Capacity DESC;
3. Highest charges
SELECT MAX(Charges) FROM CARDEN;
4. Customer name with car name
SELECT Cname, CarName
FROM CUSTOMER , CARDEN
WHERE [Link] = [Link];
7(b) Outputs
Given CARDEN Table Data:
| Make values → Suzuki, Tata, Toyota, Suzuki, Mercedes |
[Link] COUNT(DISTINCT Make) FROM CARDEN;
Output: 4
(Suzuki, Tata, Toyota, Mercedes)
[Link] MAX(Charges), MIN(Charges) FROM CARDEN;
Charges → 14, 12, 15, 14, 35
Output: 35, 12
3. SELECT COUNT(*), Make FROM CARDEN;
⚠️ Error (Make must be used with GROUP BY)
Correct form:
SELECT COUNT(*), Make FROM CARDEN GROUP BY Make;
[Link] CarName FROM CARDEN WHERE Capacity = 4;
Cars with capacity 4 → SX4, C-Class
9(a) SQL Commands
(i) Display all items in ascending order of StockDate
SELECT * FROM STOCK
ORDER BY StockDate ASC;
(ii) Display ItemNo and ItemName where UnitPrice > 10
SELECT ItemNo, ItemName
FROM STOCK
WHERE UnitPrice > 10;
(iii) Items where Dcode = 102 OR Qty > 100
SELECT *
FROM STOCK
WHERE Dcode = 102 OR Qty > 100;
(iv) Maximum UnitPrice for each dealer
SELECT Dcode, MAX(UnitPrice)
FROM STOCK
GROUP BY Dcode;
9(b) Outputs
(i)SELECT COUNT(DISTINCT Dcode) FROM STOCK;
Distinct Dcodes = 101, 102, 103
Output: 3
(ii)SELECT Qty * UnitPrice FROM STOCK WHERE ItemNo = 5006;
For ItemNo 5006 → Qty = 200, UnitPrice = 22
Output: 200 × 22 = 4400
(iii)SELECT ItemName, Dname
FROM STOCK S, DEALERS D
WHERE [Link] = [Link] AND ItemNo = 5004;
ItemNo 5004 → Eraser Big, Dcode = 102
Dealer with Dcode 102 → Clear Deals
Output:
ItemName Dname
Eraser Big Clear Deals
(iv) SELECT MIN(StockDate) FROM STOCK;
Dates → earliest = 23-Jan-09
Output: 23-Jan-09
10(i) Candidate Keys
Table columns: Pno, Name, Qty, PurchaseDate
 Candidate Key: Pno (unique for each record)

You might also like