Python Tutorial: MySQL Worksheet-1

MySQL Assignment

1. Consider the following tables GAMES. Write SQL commands for the statements (a) & (c):

GCode	GameName	Number	PrizeMoney	ScheduleDate
101	Garom Board	2	5000	2007-01-23
102	Badminton	2	12000	2008-12-12
103	Table Tennis	4	8000	2007-02-14
105	Chess	2	9000	2008-01-01
108	Lawn Tennis	4	25000	2007-03-19

(a) To display the name of all Games.

Ans : Select GameName from Games;

(b) To display details of those games which are having PrizeMoney more than 7000.

Ans : Select * from Games where Prizemoney>7000;

Ans: Select * from Games order by ScheduleDate;

(d) To display day of week those Prize money is less than 10000.

Ans: Select dayofweek(Scheduledate) from Games where prizemoney<10000;

(e) To Display the all Record those prizeMoney in 4000,5000,9000,10000,15000.

Ans: Select * from Games where prizemoney in (4000,5000,9000,10000,15000);

(f) To display the prizeMoney with Rs. 100 increment.

Ans : Select prizemoney,prizemoney+100 from Games;

(g) To display the GameName last 5 characters.

Ans: Select right(GameName,5) from Games;

2. Write the commands for the queres (a) to (f) and write the output of the SQL commands given in (g) bases on relation EMPNEW:

RELATION: EMPNEW

No	Name	Salary	Area	Age	Grade	Dept
1	Keshar store	40000	West	45	C	Civil
2	Kirti arts	35000	South	38	A	Elec
3	Kripple	60000	North	52	B	Civil
4	Aryan stall	38000	North	29	B	Civil
5	Samsons	42000	East	35	A	Comp
6	Biswal	29000	South	34	A	Mech

(a) To display the names of all employees who are in the area south.

(b) To display name and age of all employees whose age>40.

Ans SELECT * from empnew where salary>=30000 and salary<=40000;

SELECT * from empnew where salary between(30000 and 40000)

(d) To display the employee’s names on ascending order of age.

(e) To display the record those age between 33 and 45.

(f) To display name, Salary and show to calculate 33% of Salary in new column HRA.

Ans : Select Name,Salary, salary*0.33 as ‘HRA’ from empnew;

(g) Update the salary for Grade B employee by increasing Rs. 1000.

Ans: Update empnew set salary=salary+1000 where grade= ‘B’;

(h) To display the current date & Time.

Select now();

(i) To insert a new row with the following data:

7, ‘Tarik Singh’, 45000, ‘South’, 45, ‘C’, ‘Elec’.

Ans: Insert into empnew(7, ‘Tarik Singh’, 45000, ‘South’, 45, ‘C’, ‘Elec’);

3. Create database named as FAMILY using SQL command of the following column:

No, Name, FemaleMemebers,MaleMembers,Income

(i) Take appropriate data type and constraints while creating the table.

(ii) Insert 3 records into table.

(iii) Add a new field name as Occupation of type 2 characters.

4. Create database named as LAB with appropriate data types and constraint using SQL command of the following columns:

No, ItemNAme, CostPerItem, Quantity, DateofPruchase, Warranty, Operational

Take appropriate data type and constraints while creating the table.

5. Write SQL commands for the queries (a) to (g) of the following relation:

RELATION: FRIENDS

No	Name	Age	Dept	Dateofadm	Fee	Sex
1	Pankaj	24	Computer	2007-01-10	120	M
2	Shalini	21	History	2008-03-24	200	F
3	Sanjay	22	Hindi	2006-12-12	300	M
4	Sudha	25	History	2009-07-01	400	F
5	Rakesh	22	Hindi	2007-09-05	250	M
6	Shakeel	30	History	2008-01-27	300	M
7	Surya	34	Computer	2007-02-25	210	M
8	Shikha	23	Hindi	2007-07-31	200	F

(a) To show all the information about the student of History dept.

Ans: SELECT * FROM FRIENDS WHERE DEPT= ‘HISTORY’;

(b) To list the names of female students who are in Hindi dept.

Ans: SELECT NAME FROM FRIENDS WHERE DEPT= ‘HINDI’ AND SEX=’F’;

ANS : SELECT NAME FROM FRIENDS ORDER BY Dateofadm;

(d) To display student’s name, fee, age for male students only.

ANS: SELECT NAME, FEE, AGE FROM FRIENDS WHERE SEX= ‘M’;

(e) To count the number of students with age<23.

ANS : SELECT COUNT(*) FROM WHERE AGE<23;

(f) Modify the rows for computer department’s fees by increasing Rs. 100.

ANS : UPDATE FRIENDS SET FEE=FEE+100 WHERE DEPT= ‘Computer’;

(g) To display name of day those Fees is less than or equal300.

SELECT DAYNAME(DATEOFADM) FROM FRIENDS WHERE FEE<=300;

(h) To display the Month name first three characters those who are female.

SELECT MONTHNAME(LEFT(DATEOFADM),3) FROM FRIENDS WHERE SEX= ‘F’;

(i) To show the output of following Query:-

(a) SELECT name, age,dateofadm,dateofadm+2 FROM FriendsWhere Sex= ‘M’;

NAME AGE DATEOFADM DATEOFADM+2

Pankaj 24 2007-01-10 2007-01-12

Sanjay 21 2006-12-12 2006-12-14

Rakesh 22 2007-09-05 2007-09-07

Surya 25 2007-02-25 2007-02-27

Shakeel 30 2008-01-27 2008-01-29

(b) SELECT name,dateofadm,dateofadm +565/24 FROM Friends Where Sex= ‘F’

(j) To insert a new row in the table with following data:

9, ‘Zaheer’, 36, ‘Computer’, ’12-Mar-95’, 230, ‘M’.

6. Write a SQL commands for the queries (a) to (e) of the following relation:

RELATION: TOY_SHOPPE

SNo	Toy_Name	Category	Price	Quantity	Statting_Age	Ending_Age
1	Popeye	Stuff Toy	450	12	0	1
2	Rapid Fire	Two Players	600	25	5	7
3	Teddy Bear	Stuff Toy	300	40	1	5
4	Creative Mind	Building Blocks	800	18	5	8
5	Ping Pong	Two Players	500	53	5	9
6	Race-Trace	Mechanical	1800	17	9	12

(a) Display the names of all Toys, which are for two players.

(b) To display the names of toys and their Price having quantity more than 20

(d) To display a report with Toy_name, Category, Price and Total Value for only those toys for which the Starting_Age is above 1 and Ending_Age is below 9.

(e) To display the current Date & Time.

SELECT NOW();

(f) To calculate the average of price.

SELECT AVG(PRICE) FROM TOY_SHOPPE;

(g) To calculate the sum of Quantity those toys categories are Stuff Toy.

ANS: SELECT SUM(QUANTITY) FROM TOY_SHOPPE WHERE CATEGORY= ‘STUFF TOY’;

(h) To insert a new item in the existing table with the following data:

7, Jerry, Stuff Toy, 600, 15, 1, 6

INSERT INTO TOY_SHOPPE VALUES(7,‘Jerry’, ‘Stuff Toy’,600,15,1,6)

7. Write SQL Commands for the queries (a) to (g) & output (h) to (i) for relation STIPEND :

RELATION: STIPEND

No	Name	Stipend	Stream	Avgmark	Grade	Class
1	Neha	450.00	Medical	89.2	A	11C
2	Damini	400.00	Commerce	78.5	B	12B
3	Gaurav	250.00	Humanities	64.4	C	11A
4	Anu	300.00	Commerce	67.5	C	12B
5	Vikas	500.00	Non-Medical	92.0	A	12A
6	Rubina	450.00	Non-Medical	88.5	A	12A

(a) To display the name of all students who are in Medical Stream.

(b) To display name and average of all the students having Avg<70.0.

(d) To display a report with Name, Marks for each student in the table. Marks are calculated as AvgMarks*5.

(e) To count the Stream but do not show duplicate value(s).

(f) To display the Stream and the number of students of each stream in the table STUDENT.

(g) Update stipend for the rows of Graqde A students by increasing Rs. 100.

(h) To insert a new student in the table STUDENT with the following data:

7, ‘Sabina’, 500.00, ‘Non-medical’, 90.6, ‘A’, ‘11A’

(i) SELECT Name, RIGHT(UPPER(Stream),5) FROM Stipend

(j) SELECT Name, SUBSTR(Stream, 2,5) FROM Stipend WHERE Grade= ‘A’;

8. Write a SQL commands for the queries (a) to (f) and output (g) to (k)of the following relation:RELATION: BOOKHOUSE

No	Title	Author	Subject	Publisher	Quantity	Price
1	Data structure	Lipschute	DS	McGraw	4	217.00
2	DOS Guide	NORTRON	OS	PHI	3	175.00
3	Turboc C++	Robot Lafore	Prog	Galgotia	5	270.00
4	JAVA	Norton	Prog	NULL	NULL	500.00
5	Dbase Dummies	Palmer	DBMS	PustakM	7	130.00
6	Mastering Windows	Cowart	OS	BPB	1	225.00
7	Computer Studies	French	FND	Calgotia	2	75.00
8	COBOL	Stern	Prog	JohnW	4	1000.00
9	Guide Network	Freed	NET	Zpress	3	200.00
10	Basic for Beginners	Norton	Prog	BPB	3	40.00
11	Advanced Pascal	Schidt	Prog	McGraw	4	350.00

(a) To display the title of all books with Price between 100 and 300.

(b) To Display Title and Author of all the books having type Prog and published by BPB.

(d) To Count the Records.

(e) To display a report with Title, Price in descending order of price.

(f) To insert new book in the table LIBRARY with the following data:

11, ‘Java Black Book’, ‘Steven Holzner’, ‘Prog’, DreamTech’, 5, 450.00

(g) SELECT CHAR(65,86,73,82,65);

(h) SELECT CHAR(73,110,102,111);

(i) SELECT COUNT(Quantity) from BookHouse;

(j) SELECT LEFT(LCASE(Title),4) FROM Bookhouse WHERE Quantity<=3;

(k) SELECT Title, Length(Subject) as ‘Length’ from BookHouse WHERE Quantity<=3;

9. Write a SQL commands for the queries (a) to (d) & (e) to ….. of the following Table:

TABLE : CONSIGNER

CnorD	CnorName	CnorAddress	City
ND01	R. Singhal	24, ABC Enclave	New Delhi
ND02	Amit Kumar	123, Palm Avenue	New Delhi
MU15	R. Kohli	5/A South Street	Mumbai
MU50	S. Kaur	27-K, Westend	Mumbai

(a) To display the names of all Consigners from Mumbai.

(b) Display all unique cities from the table.

(d) Arrange the rows on ascending order of City.

(e) SELECT CnorName, INSTR(CnorAddress, ‘A’) FROM Consigner;

(f) SELECT CONCAT(‘My Name is ’, CnorNAme) FROM Consigner;

10. Create database TOYSHOP and table as TOY using SQL command of the following structure:

Field Name	Field Type	Constraint
Sno	Integer	NOT NULL
Toy_Name	Character(20)
Category	Character(20)
Price	SMALLINT
Quantity	Integer
Starting_Age	Integer
Ending_Age	Integer

(i) Insert at least 5 records into the TOY table.

(ii) Write a command to describe the structure of the above table.

(iii) show all records.

(iv) Drop the constraint in the field SNO.

(v) Modify the column Price as FLOAT.

(vi) Drop the column Starting_Age.

(vii) Rename Field Name Quantity to Qty.

(viii) To insert NULL record. Column Price & Quantity should be NULL.

(ix) To display the record those price is not null.

(x) To display Toy Name, price, quantity with add new alias Column ‘TotalAmount’ which are calculate Price*Quantity.

(xi) To add Primary Key of column SNo.

11. Write SQL commands to create the table HOSPITAL with the following specification:

Field Name	Field Type	Constraint
PNo	Number(4)	Primary Key
Name	Char(20)
Age	Integer
Department	Char(15)	Default ‘Comp’
Dateofadm	Date
Charges	Float	<1000
Sex	Char(1)

(i) Insert the 5 records

(ii) Write a command to describe the structure of the above table.

(iii) Add one more column in the above table as Address of type char(20).

(iv) Modify the column Address as varchar(30).

(v) Drop the column Address.

(vi) Drop primary key.

12. Write SQL commands to create the table STUDENT with the following specification:

Field Name	Field Type	Width	Constraint
Roll_No	Integer		NOT NULL Primary Key
Name	Varchar	25
Class	Character	4
Section	Character	1
DOB	Date
Total	Smallint
Percentage	Float
Grade	Character	1

Mr. Manojcreates a table Student but he is missing some requirements. Please help to solve his troubles.

(a) Show all tables.

(b) To display the structure.

(d) He does not want that blank Name column. Please help which constraint to add.

(e) He wants to change data type column Total SMALLINT to MEDIUMINT.

(f) He wants to change the column Name to Stud_Name.

(g) To change size address 30.

(h) To remove the column grade.

(i) Create a new table Teachers with appropriate data types and constraint using SQL command of the following columns:

Tno,Tname,tAddress,salary,dept_no,doj

(j) To add constraints FOREIGNKEY & references Table Teacher column tno.

(k) To remove foreign key.

(l) To Rename table STUDENT to STUDENTS.

(m) To insert values only column rollno, stud_name, address.

Python Tutorial

Monday, 7 March 2022

MySQL Worksheet-1

No comments:

Post a Comment