Scribd
Upload
16 views14 pages

prac file (hy) sql

The document provides SQL commands for various operations including querying functions, creating and modifying tables, and performing updates on a grocery shop's product database. It also includes commands related to a car showroom database and a student-stream database, detailing how to create tables, apply constraints, and perform data manipulation. Additionally, it discusses the Cartesian product of tables and the resulting degree and cardinality of the modified tables.

Uploaded by

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

prac file (hy) sql

The document provides SQL commands for various operations including querying functions, creating and modifying tables, and performing updates on a grocery shop's product database. It also includes commands related to a car showroom database and a student-stream database, detailing how to create tables, apply constraints, and perform data manipulation. Additionally, it discusses the Cartesian product of tables and the resulting degree and cardinality of the modified tables.

Uploaded by

J PLAYZZ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 14

Informatics

Practices Chapter-1
Querying and SQL Functions

Write the output produced by the following SQL


commands:
a) SELECT POW(2,3);

b) SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);

c) SELECT LENGTH("Informatics Practices");

d) SELECT YEAR(“1979/11/26”),
MONTH(“1979/11/26”), DAY(“1979/11/26”),
MONTHNAME(“1979/11/26”);

e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4);

f) SELECT MID("Informatics",3,4), SUBSTR("Practices",3);


Consider the following table named “Product”, showing
details of products being sold in a grocery shop.

a) Write SQL queries for the following:

i. Create the table Product with


appropriate data types and
constraints.

>>CREATE TABLE Product


(PCode CHAR(3) PRIMARY KEY,
PName
VARCHAR(50), UPrice
DECIMAL(10, 2),
Manufacturer VARCHAR(50));

ii. List the Product Code, Product


name and price in descending order of
their product name. If PName is the
same then display the data in
ascending order of price.

>>SELECT PCode, PName, UPrice


FROM Product
ORDER BY PName DESC, UPrice ASC;
iii.Add a new column Discount to the
table Product.

>>ALTER TABLE Product


ADD Discount FLOAT(10,

2);

iv.Calculate the value of the discount


in the table Product as 10 per cent of
the UPrice for all those products
where the UPrice is more than 100,
otherwise the discount will be 0.

>>UPDATE Product
SET Discount = UPrice * 0.10
WHERE UPrice > 100;
v.Increase the price by 12 per cent for
all the products manufactured by
Dove.

>>UPDATE Product
SET UPrice = UPrice * 1.12
WHERE Manufacturer =

'Dove';

vi. Display the total number of


products manufactured by each
manufacturer.

>>SELECT Manufacturer, COUNT(*)


FROM Product
GROUP BY Manufacturer;

b) Write the output(s) produced by executing


the following queries on the basis of the
information given above in the table Product:

i. SELECT PName, Average(UPrice)


FROM Product GROUP BY Pname;
ii. SELECT DISTINCT Manufacturer
FROM Product;

iii. SELECT COUNT(DISTINCT PName) FROM Notes


Product;

iv.SELECT PName, MAX(UPrice),


MIN(UPrice) FROM Product GROUP BY

PName;
Using the CARSHOWROOM database given in the chapter,
write the SQL queries for the following:
a) Add a new column Discount in the INVENTORY table.
>>ALTER TABLE INVENTORY
ADD Discount DECIMAL(5,2);

b) Set appropriate discount values for all cars keeping


in mind the following:
(i) No discount is available on the LXI model.
(ii)VXI model gives a 10% discount.
(iii) A 12% discount is given on cars other than LXI
model and VXI model.

>>UPDATE INVENTORY
SET Discount = 0
WHERE Model = 'LXI';
>>UPDATE INVENTORY
SET Discount = 10
WHERE Model = 'VXI';
>>UPDATE INVENTORY
SET Discount = 12
WHERE Model NOT IN ('LXI', 'VXI');
c) Display the name of the costliest car with fuel type
“Petrol”.

>>SELECT CarId,
MAX(CarName) FROM
INVENTORY
WHERE FuelType = 'Petrol';

d) Calculate the average discount and total discount


available on Car4.

>>SELECT AVG(Discount), SUM(Discount)


FROM INVENTORY
WHERE CarName = 'Car4';

e) List the total number of cars having no discount


>>SELECT COUNT(*)
FROM INVENTORY
WHERE Discount = 0;

Consider the following tables Student and Stream in


the Streams_of_Students database.
The primary key of the Stream table is StCode (stream
code) which is the foreign key in the Student
table.The primary key of the Student table is AdmNo
(admission number).
Write SQL queries for the following:

a) Create the database Streams_Of_Students.

>> CREATE DATABASE Streams_Of_Students;

b) Create the table Student by choosing appropriate


data types based on the data given in the table.

>>CREATE TABLE Student ( AdmNo INT PRIMARY


KEY, Name VARCHAR(50), StCode CHAR(3));

c) Identify the Primary keys from tables Student and


Stream. Also, identify the foreign key from the table
Stream.

Primary Key of Student:


AdmNo Primary Key of
Stream: StCode Foreign
Key of Student: StCode

d) Jay has now changed his stream to Humanities.


Write an appropriate SQL query to reflect this
change.

>>UPDATE
Student SET
StCode = 'S03'
WHERE Name = 'Jay';
e) Display the names of students whose names end
with the character ‘a’. Also, arrange the students in
alphabetical order.

>>SELECT Name FROM Student


WHERE Name LIKE '%a' ORDER BY Name;

f) Display the names of students enrolled in Science


and Humanities stream, ordered by student name in
alphabetical order, then by admission number in
ascending order (for duplicating names).

>>SELECT Name
FROM Student
WHERE StCode IN ('S01', 'S03')
ORDER BY Name, AdmNo;
g) List the number of students in each stream
having more than 1 student.

>>SELECT Stream, COUNT(*)


FROM Student S JOIN Stream ST ON S.StCode = ST.StCode
GROUP BY Stream
HAVING COUNT(*) > 1;

h) Display the names of students enrolled in different


streams, where students are arranged in descending
order of admission number.

>>SELECT Name, Stream


FROM Student S JOIN Stream ST ON S.StCode = ST.StCode
ORDER BY AdmNo DESC;

i) Show the Cartesian product on the Student and


Stream table. Also mention the degree and cardinality
produced after applying the Cartesian product.

>>SELECT * FROM Student, Stream;


j) Add a new column ‘TeacherIncharge” in the
Stream table. Insert appropriate data in each row.

>>ALTER TABLE Stream


ADD TeacherIncharge VARCHAR(50);
>>UPDATE Stream
SET TeacherIncharge = 'Mr. Sharma' WHERE StCode = 'S01';
>>UPDATE Stream
SET TeacherIncharge = 'Ms. Singh' WHERE StCode = 'S02';
>>UPDATE Stream
SET TeacherIncharge = 'Mr. Gupta' WHERE StCode = 'S03';

k) List the names of teachers and students.

>>SELECT Name FROM Student UNION SELECT


TeacherIncharge FROM Stream;
l) If the Cartesian product is again applied on
Student and Stream tables, what will be the degree
and cardinality of this modified table?

After adding the TeacherIncharge


column: Degree: 6
Cardinality: 18

You might also like