Class XII – Informatics Practices (IP)
Code No. 065
Lab Assignment
Topic: Database Concepts and the Structured Query Language (SQL)
_________________________________________________________________________________________________
Database: Electronics
1. Create Database Electronics
CREATE DATABASE Electronics;
2. Use the created database;
USE Electronics;
3. Create the Item table
CREATE TABLE Item ( ItemCode VARCHAR(10) PRIMARY KEY, ItemName VARCHAR(30), Price INT );
4. Create the Customer table
CREATE TABLE Customer ( CustCode VARCHAR(10) PRIMARY KEY, CustName VARCHAR(40),
City VARCHAR(30), ItemCode VARCHAR(10), FOREIGN KEY (ItemCode) REFERENCES
Item(ItemCode) );
5. Insert data into Item table
INSERT INTO Item VALUES
('PC01', 'PC HP', 35000),
('LC01', 'LAPTOP SONY', 35000),
('PC02', 'PC ASUS', 32000),
('PC03', 'PC HCL', 37000),
('LC02', 'LAPTOP TOSHIBA', 57000);
6. Insert data into Customer table
INSERT INTO Customer VALUES
('C01', 'N ROY', 'DELHI', 'LC02'),
('C06', 'H SINGH', 'MUMBAI', 'PC03'),
('C12', 'R. PANDEY', 'DELHI', 'PC02'),
('C15', 'C. SHARMA', 'DELHI', 'LC01'),
('C16', 'K. AGARWAL', 'BENGALURU', 'PC01'),
('C17', 'SHRUTI SINGH', 'BENGALURU', 'PC01');
7. View the structure of the table
DESC Item;
DESC Customer;
8. Display entire content of the tables
SELECT * FROM Customer;
SELECT * FROM Item;
9. Update the price of ItemCode ‘PC03’ to 40000
UPDATE Item SET Price = 40000 WHERE ItemCode = 'PC03';
10. Display details of those Customers whose city is Delhi and who purchased ‘PC HP’
SELECT C.*
FROM Customer C
JOIN Item I ON [Link] = [Link]
WHERE [Link] = 'DELHI' AND [Link] = 'PC HP';
11. Display details of the item purchased by Custcode ‘C15’
SELECT I.*
FROM Item I JOIN Customer C ON [Link] = [Link]
WHERE [Link] = 'C15';
12. Display customer code, customer name and price for all sales
SELECT [Link], [Link], [Link]
FROM Customer C
JOIN Item I ON [Link] = [Link];
13. Display details of customers who purchased PC of any company
SELECT C.*
FROM Customer C
JOIN Item I ON [Link] = [Link]
WHERE [Link] LIKE 'PC%';
14. Display information of all customers whose name starts with ‘K’
SELECT * FROM Customer WHERE CustName LIKE 'K%';
15. Display name of all customers whose name ends with ‘SINGH’
SELECT CustName FROM Customer WHERE CustName LIKE '%SINGH';
16. Extract the last two digits of the ItemCode
SELECT ItemCode, RIGHT(ItemCode, 2) AS LastTwoDigits FROM Item;
17. Display the name of all customers containing 'A' as the second last character
SELECT CustName FROM Customer
WHERE SUBSTRING(CustName, LENGTH(CustName)-1, 1) = 'A';
18. Find the number of customers living in Delhi
SELECT COUNT(*) AS NoOfCustomersDelhi
FROM Customer
WHERE City = 'DELHI';
19. Find the total number of items available
SELECT COUNT(*) AS TotalItems FROM Item;
20. Find the Average Sales of the items
SELECT AVG(Price) AS AverageItemPrice FROM Item;
Submitted By:
Name: ____________________
Class: XII
Subject: Informatics Practices
Assignment: SQL Lab Work
Teacher’s Signature: ____________________