PRACTICAL FILE
.docx
keyboard_arrow_up
School
University of California, Santa Cruz *
*We aren’t endorsed by this school
Course
3
Subject
Information Systems
Date
Jun 14, 2024
Type
docx
Pages
16
Uploaded by ColonelOwlPerson28
PROGRAM NO 1
Problem Statement(Question-1):
1. Create a products table (Pno, Pname, Rate, QOH) in MySQL and insert data. Implement the
following SQL commands on the products table:
Source Code:
create database Q;
use Q;
create table products
(Pno int,
Pname varchar(30),
Rate int,
QOH int);
INSERT INTO products VALUES(1,'MAGGIE',30,4);
INSERT INTO products VALUES(2,'OREO',25,5);
INSERT INTO products VALUES(3,'LAYS',20,3);
INSERT INTO products VALUES(4,'SUGAR',120,2);
INSERT INTO products VALUES(5,'TATA SALT',120,3);
COMMIT;
select*from products; Output:
Problem Statement(Question-1):
GROUP BY and find the min, max, sum, count and average
Source Code:
select Rate,COUNT(*)
from products
GROUP BY Rate;
SELECT COUNT(Rate) FROM products WHERE Rate=120;
SELECT MIN(Rate) FROM products;
SELECT MAX(Rate) FROM products;
SELECT SUM(Rate) FROM products;
SELECT AVG(Rate) FROM products;
Output:
Problem Statement(Question-1):
DELETE to remove tuple(s)
Source Code:
DELETE FROM products where Pname='MAGGIE';
Output:
Problem Statement(Question-1):
ORDER By to display data in ascending / descending order
Source Code:
select*from products
order by Rate DESC;
select*from products
order by Rate ASC;
Output:
Problem Statement(Question-1):
UPDATE table to modify data
Source Code:
update products
set Pname='SUGAR',Rate=50
where Pno=1;
Output:
Problem Statement(Question-1):
ALTER table to add new attributes / modify data type / drop attribute
Source Code:
alter table products
add Cname varchar(15);
Output:
Source Code:
alter table products
drop column QOH;
Output:
PROGRAM 3
3. Write SQL Commands for the following on the basis of the given table GRADUATE:
SOURCE CODE FOR THE TABLE:
SOURCE CODE
Create table GRADUATE
(SLNo INTEGER,
NAME Varchar(25),
STIPEND INTEGER,
SUBJECT VARCHAR(30),
AVERAGE INTEGER,
RANK INTEGER);
Insert into GRADUATE values(1,'KARAN',400,'PHYSICS',68,1);
Insert into GRADUATE values(2,'RAJ',450,'CHEMISTRY',68,1);
Insert into GRADUATE values(4,'DIVYA',350,'CHEMISTRY',63,1);
Insert into GRADUATE values(5,'GAURAV',500,'PHYSICS'70,1);
Insert into GRADUATE values(6,'MANAV',400,'CHEMISTRY',55,2);
Insert into GRADUATE values(7,'VARUN',250,'MATHS',64,1);
Insert into GRADUATE values(8,'LIZA',450,'COMPUTER',68,1);
Insert into GRADUATE values(9,'PUJA',500,'PHYSICS',62,1);
Insert into GRADUATE values(8,'NISHA',300,'COMPUTER',57,2);
OUTPUT: (i)
List the names of those students who have obtained rank 1 sorted by NAME.
SOURCE CODE:
SELECT NAME FROM GRADUATE WHERE RANK=1 ORDER BY NAME;
OUTPUT:
(ii)
Display a list of all those names whose AVERAGE is greater than 65.
SOURCE CODE
: SELECT NAME FROM GRADUATE WHERE AVERAGE>65;
OUTPUT
:
(iii) Display the names of those students who have opted COMPUTER as a SUBJECT with an
AVERAGE of more than 60.
SOURCE CODE:
SELECT NAME FROM GRADUATE WHERE SUBJECT=’COMPUTER’ AND AVERAGE>60;
OUTPUT
: (IV)
List the names of all the students in alphabetical order.
SOURCE CODE
: SELECT NAME FROM GRADUATE ORDER BY NAME;
OUTPUT:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help