Basic SQL

 

Guide to SQL for beginners:-

•       Consider the following MOVIE table and write the SQL queries based on it.

Movie_ID	MovieName	Type	ReleaseDate	ProductionCost	BusinessCost
M001	Dahek	Action	2022/01/26	1245000	1300000
M002	Attack	Action	2022/01/28	1120000	1250000
M003	Looop Lapeta	Thriller	2022/02/01	250000	300000
M004	Badhai Do	Drama	2022/02/04	720000	68000
M005	Shabaash Mithu	Biography	2022/02/04	1000000	800000
M006	Gehraiyaan	Romance	2022/02/11	150000	120000

•         Display all information from movie.

•         Display the type of movies.

•         Display movieid, moviename, total_eraning by showing the business done by the movies.

•         Display movieid, moviename and productioncost for all movies with productioncost greater thatn 150000 and less than 1000000.

•         Display the movie of type action and romance.

•         Display the list of movies which are going to release in February, 2022.

1       Consider the given table patient and Write following queries:

 

•         Display the total charges of patient admitted in the month of November.

 

•         Display the eldest patient with name and age. 

•         Count the unique departments.

 

•         Display an average charges.

3.Suppose your school management has decided to conduct cricket matches between students of Class XI and Class XII. Students of each class are asked to join any one of the four teams – Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

•         Create a database “Sports”.

•         Create a table “TEAM” with following considerations:

•         It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.

•         Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

•         Using table level constraint, make TeamID as the primary key.

•         Show the structure of the table TEAM using a SQL statement.

•         As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

•         Row 1: (1, Tehlka)

•         Row 2: (2, Toofan)

•         Row 3: (3, Aandhi)

•         Row 3: (4, Shailab)

e)    Show the contents of the table TEAM using a DML statement.

f)     Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

MatchID	MatchDate	FirstTeamID	SecondTeamID	FirstTeamScore	SecondTeamScore
M1	2021/12/20	1	2	107	93
M2	2021/12/21	3	4	156	158
M3	2021/12/22	1	3	86	81
M4	2021/12/23	2	4	65	67
M5	2021/12/24	1	4	52	88
M6	2021/12/25	2	3	97	68

 

4.    Write following queries:

a)    Display the matchid, teamid, team score who scored more than 70 in first inning along with team name.

b)    Display matchid, teamname and secondteamscore between 100 to 160.

c)    Display matchid, teamnames along with matchdates. 

d)    Display unique team names

e)    Display matchid and matchdate played by Anadhi and Shailab.

 

5.          Consider the following table and write the queries:

 

itemno	Item	dcode	qty	unitprice	stockdate
S005	Ballpen	102	100	10	2018/04/22
S003	Gel Pen	101	150	15	2018/03/18
S002	Pencil	102	125	5	2018/02/25
S006	Eraser	101	200	3	2018/01/12
S001	Sharpner	103	210	5	2018/06/11
S004	Compass	102	60	35	2018/05/10
S009	A4 Papers	102	160	5	2018/07/17

 

a)    Display all the items in the ascending order of stockdate.

 

b)    Display maximum price of items for each dealer individually as per dcode from stock.

c)    Display all the items in descending orders of item names.

d)    Display average price of items for each dealer individually as per doce from stock which average price is more than 5.

e)    Display the sum of quantity for each dcode. 

 

 

 

21 Write a MySQL connectivity program in Python to

• Create a database school

• Create a table students with the specifications -

ROLLNO integer, STNAME character(10) in MySQL

and perform the following operations: o Insert two

records in it o Display the contents of the table

 

ALL BASIC COMMANDS FOR SQL:

Blue=commands heading , red=command , default=black

Creation and display of database and table:

Create database <database_name>;

Show databases;

Use <database_name>;

Create table <table_name>(<column_name >  columndatatype)

>(column data types can be   int,char(limit),varchar(limit),decimal(4,2))

Inserting values into table:

(let’s assume we are making table for student details)

Insert into <table_name>(R_no , S_name,Percentage,ADM_date) values(1,”Aman”,96.64,”DD/MM/YYYY”)

Desc <table_name>;

For changes in table:

Drop database <database_name>;

Drop table <table_name>;

Drop column <column_name>;

Update <table_name> set Sname=”Ram” where Sname=”Aman”;

Alter table <table_name> add <column_name> <datatype> <size>;

Alter table change [<column_name>] new_name varchar(20);

Display Commands:

Select *from <table_name>;

Select Sname,R_no,marks from <table_name>;

Where and Distinct Clause:

Select *from <table_name> where Sname=” ” ;

     “”     “”            “”          “” where marks>    ;

Select distinct name from <table_name>;

Select score/500*100 as “scored_percantage” from <table_name>;

Between and Not Between clause:

Select name,Roll_no,marks from <table_name> where marks between/not between 80 and 90;

In and Not in ,identifying datatypes clause:

Select *from <table_name> where Roll_no in (1,2,3);

“”            “”              “”                “”    not in

“”            “”              “”                “”     name like “A%”; (if name starts with A) 

“”             “”              “”                “”     “_ _ _%”; (atleast 3 charcters)  

“”             “”              “’                  “”    “%jeet%”; (name contain substring ‘jeet’) 

Order by clause:

Order by ordering_func,(default ascending , for descending “desc”)

Select R_no,name from <table_name> where marks>75 order by name/name desc;

Delete(for deleting rows):

Delete from <table_name> where name=”Ram”;

Aggregate func:

Select max(score) as “total_marks” from <table_name>;

‘’          min(      )           ‘’

‘’           Sum(    )            ‘’

‘’          avg(   )            ‘’

 Group by:

Select name,dept_no,count(*) from <table_name> group by dept_no,

Select name,dept having sum(  ) between 80 and 90;

 

 

Table Join:

Select *from <table1>,<table2>;  (cartesian product)

Select patient_no , Dname from <table1>,<table2> where p_no=d_no;