MySQL----Basic SQL Exercises

Practice Script:

create database filmclub; use filmclub; Create table category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname  VARCHAR (20)); Create table film ( fid INT PRIMARY KEY AUTO_INCREMENT, fname  VARCHAR ( director varchar),,    #导演  price decimal (10,2),  showtime  date, cid int, foreign key (CID) ( references category); Create table user ( uid INT PRIMARY KEY AUTO_INCREMENT, uname  VARCHAR (a),  birthday date, gender enum (' Male ', ' female '),  address varchar (, ) Cellphone char (11)); Create table user_category (  uid int,  cid int,  foreign  key (UID)  references user (UID),   foreign key (CID)  references category ( CID),   primary key (Uid,cid)); Create table emp (  eid int primary key auto_increment,  ename varchar (20 ),   gender enum (' Male ', ' female '),   hiredate date,  sal decimal (10,2),   address varchar (20)); Create table sal_grade (  gid INT PRIMARY KEY AUTO_INCREMENT,   minsal decimal (10,2),   maxsal decimal (10,2)); Create table orders (  eid int,  uid int,  fid int,   num int,  odate date); Insert into category values (NULL,  ' Comedy '); Insert into category values (null,  ' action '); Insert into category values ( null,  ' suspense '); Insert into category values (null,  ' terror '); insert into category  values (null,  ' sci-fi '); Insert into category values (null,  ' War '); INSERT INTO  category values (NULL,  ' Love '); Insert into category values (null,  ' catastrophe '); Insert into film values (NULL,   ' World Without Thieves ', ' Feng Xiaogang ', ' 2008-12-12 ', 1, insert into film values (null,  ' Kung Fu ', ' Stephen Chow Chi ', 150, ' 2009-12-12 ', 2); Insert into film values (null,  ' Big Talk Tour ', ' Stephen Chow Chi ', ' 2012-3-12 ', 3);insert  Into film values (null,  ' I am not pan Jinlian ', ' Feng Xiaogang ', ', ' 2007-5-31 ', 1); Insert into film values ( null,  ' Taoist descent ', ' Chen Kai ', ' 2004-8-9 ', 8, insert into film values (null,  ' hot pot hero ', ' Chen Kai ', 60, ' 2011-11-11 ', 7); Insert into film values (null,  ' Dragon Quest ', ' Feng Xiaogang ', ' 2007-7-7 ', 7);insert  Into film values (null,  ' Old Cannon ', ' Triumph of Chen ', ' 2005-9-2 ', 1); Insert into film values (NULL ,  ' I am a witness ', ' Stephen Chow Chi ', ' 2010-10-5 ', 2, insert into film values (null,  ' ye Q ', ' Feng Xiaogang ', 120, ' 2012-6-3 ', 3); Insert into user values (NULL, ' Liu Huan ', ' 1950-1-1 ', ' Male ', ' North Street ', ' 13312345678 ');insert  Into user valueS (NULL, ' Jacky ', ' 1955-2-3 ', ' Male ', ' South Street ', ' 13312345676 '); Insert into user values (null, ' Carina Lau ', ' 1970-11-21 ', ' female ', ' North Street ', ' 13312345675 '); Insert into user values (NULL, ' Michelle Reis ', ' 1988-9-3 ', ' female ', ' South Street ', ' 13312345673 '); Insert into user values (NULL, ' Andy Lau ', ' 1953-2-11 ', ' Male ', ' North Street ', ' 13312345672 '); INSERT  into user values (NULL, ' Zhang National ', ' 1999-12-31 ', ' Male ', ' East Avenue ', ' 13312345671 '); insert into user  values (null, ' Leslie ', ' 1988-3-23 ', ' Male ', ' West Avenue ', ' 13312345670 '); Insert into user values (null, ' Liu Jianguo ' , ' 1970-6-22 ', ' Male ', ' West Avenue ', ' 13312345679 '); insert into user_category values; insert into  user_category values (1,3); Insert into user_category values (2,8); INSERT INTO  user_category values (3,1); insert into user_category values (3,5); INSERT INTO  user_category values (3,7); insert into user_category values (2,1); INSERT INTO  user_category values (1,7); inseRt into user_category values (8,8); insert into user_category values (8,7); INSERT  into user_category values (5,7); insert into user_category values (5,3); INSERT  into user_category values (2,6); insert into user_category values (7,1); INSERT  into user_category values (7,2); insert into user_category values (7,3); INSERT  into emp values (null,  ' Guo Jing ',  ' male ',  ' 2002-2-3 ', 2500, ' East Avenue ');insert into  Emp values (null,  ' Huang Rong ',  ' female ',  ' 2003-12-3 ', 5500, ' East Avenue '); Insert into emp values ( null,  ' Yang mi ',  ' female ',  ' 2002-2-8 ', 8500, ' West Avenue '); Insert into emp values (null,  ' Liu Poetry ',   ' female ',  ' 2004-4-12 ', 6500, ' South Avenue '); Insert into sal_grade values (null, 1000, 2000 ); Insert into sal_grade values (null, 2001, 4000); Insert into sal_grade  values (null, 4001, 5000); insert into sal_grade values (null, 5001, 7000); INSERT INTO sal_ Grade values (null, 7001, 9000); Insert into orders values (1,2,10,1, ' 2016-11-11 ') ; Insert into orders values (2,3,8,2, ' 2016-2-21 '); Insert into orders values ( 3,7,10,1, ' 2016-3-21 '); Insert into orders values (1,1,7,1, ' 2016-10-15 ');insert into  Orders values (1,8,3,1, ' 2016-2-17 '); Insert into orders values (1,7,1,1, ' 2016-5-18 '); INSERT  into orders values (4,1,1,1, ' 2016-5-7 '); Insert into orders values (4,2,1,1, ' 2016-5-9 '); Insert into orders values (4,3,1,1, ' 2016-11-10 ');insert into orders  VALUES (3,5,10,1, ' 2016-5-11 '); Insert into orders values (2,1,1,1, ' 2016-7-12 '); insert into  orders values (2,1,1,2, ' 2016-7-13 '); Insert into orders values (2,1,1,3, ' 2016-7-14 '); I Nsert into orders vAlues (2,1,9,5, ' 2016-8-19 '); Insert into orders values (2,1,8,3, ' 2016-8-4 ');INSERT INTO  Orders values (2,2,5,1, ' 2016-8-6 '); Insert into orders values (2,2,6,1, ' 2016-1-22 '); INSERT  into orders values (2,2,1,1, ' 2016-1-11 '); Insert into orders values (2,6,3,1, ' 2016-2-17 '); Insert into orders values (2,6,8,2, ' 2016-3-12 '); select * from category; select * from film; select * from user; select * from user_category; select * from emp; select * from sal_grade; select * from orders;

Exercise:

#1查询价格比2号类型所有电影平均价还低的电影SELECT  * FROM film WHERE price < (Select avg (Price )  from film where cid=2); select * from film; #2查询张学友喜欢的类型中所有电影的最高价SELECT  max (price)  from film where  cid IN  (select cid from user_category where uid =  (SELECT  uid FROM USER WHERE uname =  ' Jacky ')); #3查询所有电影, and the type name of the movie, requires that all type names be displayed ( MySQL does not have a completely external connection full join, can only be used for federated queries) select * from film right join category  on film.cid = category.cidunionselect * from film left join  Category on film.cid = category.cid#4 query price more than the world No thieves in the film, each type of how many movies select cid,count (*)  FROM film WHERE price > (select price from film where  fname =  ' World Without Thieves ') group by cid; #5查询那些员工给那些会员卖了哪些电影SELECT  ename,uname,fname&nbsP from emp e,user u,film f,orders owhere e.eid = o.eid and  O.uid = u.uid and f.fid = o.fid#6 for employees who have repeat customers select eid,uid,count (*)  FROM  orders group by eid,uid having count (*)  > 1;# 7 inquiries more than one employee (table sub-query must alias in MySQL, otherwise error, Orcale do not need aliases) select eid,count (*)  FROM  (select eid,uid , COUNT (*)  from orders group by eid,uid having count (*)  > 1) Aagroup by eid#8 a movie with a price higher than the average price for all comedy types select * from film where price  > (Select avg (Price)  from film where cid = (select cid from  category where cname =  ' comedy ') #9查询电影所属类型的平均价 (related subquery) Select fname, (Select avg (Price )  from film where cid = a.cid)  AS  Average price  from film a# 10 query price than the 1th type all movie prices are high movie SELECT&NBSP;*&NBSP;FROm film where price > (Select max (Price)  from film where cid  = 1) #11查询上映日期比天下无贼晚的电影中, how many movies each director has select * from film where showtime  > (select showtime from film where fname =  ' World Without Thieves ') #12查询哪些用户都喜欢哪些类型SELECT  uname,cname from user,category,user_category where user.uid = user_ category.uid and category.cid = user_category.cid#13 query likes more than 2 kinds of users select uname  From (select uname,cname from user,category,user_category where user.uid =  USER_CATEGORY.UID&NBSP;AND&NBSP;CATEGORY.CID&NBSP;=&NBSP;USER_CATEGORY.CID) aagroup by uname  Having count (*)  > 2#14 query has never been liked by the type Select cname from category where  cid not in (select distinct cid from user_category) #15查询喜欢了所有类型的用户SELECT  uid,count (*)  from usEr_category group by uidhaving count (*)  =  (select count (*)  FROM  category) #16查询每个销售人员的销售总额SELECT  ename,sum (num*price)  FROM emp,film,orders WHERE  emp.eid = orders.eid andorders.fid = film.fidgroup by ename# 17 queries each movie is sold how many select fname,sum (num)  from film join orders on film.fid &NBSP;=&NBSP;ORDERS.FIDGROUP&NBSP;BY&NBSP;FNAME#18 Search release date than Stephen Chow director all movies are late in the movie, each type of film the lowest price select  (select  CNAME&NBSP;FROM&NBSP;CATEGORY&NBSP;WHERE&NBSP;A.CID&NBSP;=&NBSP;CID), MIN (price)  from film a  WHERE showtime > (Select max (Showtime)  from film where director  =  ' Stephen Chow Chi ') group by cid#19 query the movie Select * from film where year released in Leap year ( Showtime)  % 4 = 0 and year (Showtime)  % 100 != 0or year ( Showtime)  % 400 = 0#20 Search Release dateMid-month, Select * from film where month (Showtime)  > day (Showtime) # 21 query prices and types are the same as ' no Thieves ' movie select * from filmwhere  (cid,price)  = (select cid,price  FROM film WHERE fname =  ' World Without Thieves ' #22查询最受欢迎的类型SELECT  cid,count (*)  from  user_categorygroup by cidhaving count (*)  >= all (SELECT COUNT (*)   FROM&NBSP;USER_CATEGORYGROUP&NBSP;BY&NBSP;CID) #23查询每个用户各花了多少钱SELECT  uid,sum (num*price)  FROM  Orders,film where orders.fid = film.fid group by uid

MySQL----Basic SQL Exercises