All queries must be done online through the database created for the class except the last query. which requires you to create new tables to...

Hello, I have SQL project. There is only 11 questions and they just need some very short SQL codes. I also have the tables and the codes for SQL server (phpMyAdmin). I attached both project and table (I can also provide those codes for phpMyAdmin, to check the queries!)

DATABASE CODES:

create table zipcode (
  zip	integer(5) primary key,
  city 	varchar(30),
  State varchar(20));


create table employee (
  eno		varchar(10) primary key,
  ename 	varchar(30),
  zip		integer(5) references zipcode(zip),
  hire_date	date);



create table book (
  bno		integer(5) primary key,
  bname		varchar(30),
  qoh		integer(5) not null,
  price		dec(6,2) not null);


create table customer (
  cno 		integer(5) primary key,
  cname		varchar(30),
  street	varchar(30),
  zip		integer(5)  references zipcode(zip),
  phone		char(12));



create table salesOrder (
  ono		integer(5)  primary key,
  cno		integer(5)  references customer(cno),
  eno		varchar(10)  references employees(Eno),
  received	date,
  shipped	date
);



create table orderLine (
  ono		integer(5)  references salesOrder(ono),
  bno		integer(5)  references book(bno),
  quantity	integer(10) not null,
  primary key (ono, bno));




insert into zipcode values (98225, 'Bellingham', 'WA');
insert into zipcode values (95388, 'Winton', 'CA');
insert into zipcode values (44242, 'Stow', 'OH');
insert into zipcode values (61536, 'Hanna city', 'IL');
insert into zipcode values (01254, 'Richmond', 'MA');
insert into zipcode values (95124, 'San Jose', 'CA');
insert into zipcode values (95382, 'Turlock', 'CA');
insert into zipcode values (95380, 'Turlock', 'CA');
insert into zipcode values (98102, 'Seattle', 'WA');



insert into employee values ('P0239401', 'Jones Hoffer',98225, '2000-12-12');
insert into employee values ('P0239402', 'Jeffrey Prescott',95388, '2016-11-07');
insert into employee values ('P0239403', 'Fred NcFaddeb',95124, '2008-09-01');
insert into employee values ('P0239404', 'Karen Ives',98102, '2014-05-21');


insert into book values (10501, 'Forensic Accounting',200, 229.99);
insert into book values (10502, 'SAP Business One',159, 149.99);
insert into book values (10503, 'Fraud Cases',190, 179.99);
insert into book values (10504, 'CPA Review',65, 279.99);
insert into book values (10605, 'Quickbooks for Business',322, 59.99);
insert into book values (10704, 'Financial Accounting',129, 164.99);
insert into book values (10879, 'Managerial Accounting',155, 114.99);
insert into book values (10933, 'Cost Accounting',122, 219.99);
insert into book values (10948, 'Intermediate Accounting',123, 164.99);
insert into book values (10965, 'Accounting Information Systems',211, 259.99);
insert into book values (10988, 'XBRL in Nutshell',124, 109.99);


insert into customer values (23511, 'Michelle Kuan', '123 Main St.',98225, '360-636-5555');
insert into customer values (23512, 'George Myer', '237 Ash Ave.',95124, '312-678-5555');
insert into customer values (23513, 'Richard Gold', '111 Inwood St.',95124, '312-883-7337');
insert into customer values (23514, 'Robert Smith', '54 Gate Dr.',95388, '206-832-1221');
insert into customer values (23515, 'Christopher David', '777 Loto St.',98225, '360-458-9878');
insert into customer values (23516, 'Adam Beethoven', '234 Park Rd.',95380, '209-546-7299');
insert into customer values (23517, 'Ludwig Bach', '5790 Walnut St.',95382, '209-638-2712');
insert into customer values (23518, 'Kathleen Pedersen', '1233 Federal Ave E', 98102, '360-573-7239');


insert into salesOrder values (1020, 23511, 'P0239403', '2018-01-13', '2018-01-15');
insert into salesOrder values (1021, 23513, 'P0239401', '2018-01-13', '2018-01-16');
insert into salesOrder values (1022, 23513, 'P0239402', '2018-01-15', '2018-01-17');
insert into salesOrder values (1023, 23512, 'P0239403', '2018-01-16', '2018-01-18');
insert into salesOrder values (1024, 23511, 'P0239402', '2018-01-18', '2018-01-20');
insert into salesOrder values (1025, 23511, 'P0239403', '2018-01-29', '2017-01-31');
insert into salesOrder values (1026, 23512, 'P0239404', '2018-01-30', '2018-01-31');
insert into salesOrder values (1027, 23512, 'P0239402', '2018-01-30', '2018-01-31');
insert into salesOrder values (1028, 23512, 'P0239404', '2018-01-30', '2018-01-31');
insert into salesOrder (ONO, CNO, ENO, RECEIVED) values (1029, 23513, 'P0239402', '2018-01-31');
insert into salesOrder (ONO, CNO, ENO, RECEIVED) values (1030, 23511, 'P0239401', '2018-01-31');



insert into orderLine values (1020, 10501,7);
insert into orderLine values (1020, 10502,15);
insert into orderLine values (1020, 10504,3);
insert into orderLine values (1020, 10503,6);
insert into orderLine values (1021, 10605,4);
insert into orderLine values (1022, 10605,2);
insert into orderLine values (1022, 10704,4);
insert into orderLine values (1023, 10879,4);
insert into orderLine values (1023, 10988,19);
insert into orderLine values (1024, 10502,7);
insert into orderLine values (1024, 10988,2);
insert into orderLine values (1025, 10502,4);
insert into orderLine values (1025, 10988,3);
insert into orderLine values (1025, 10948,2);
insert into orderLine values (1026, 10965,15);
insert into orderLine values (1026, 10933,5);
insert into orderLine values (1027, 10933,21);
insert into orderLine values (1028, 10933,9);
insert into orderLine values (1028, 10965,11);
insert into orderLine values (1029, 10933,4);
insert into orderLine values (1029, 10965,10);
insert into orderLine values (1029, 10988,3);
insert into orderLine values (1030, 10965,6);

ATTACHMENT PREVIEW Download attachment

questions.jpg

All queries must be done online through the database created for the class except the last query. which
requires you to create new tables to complete. You M follow the procedure that i showed to you in
class to prepare the submission. Mon-conformity of the procedure will result In a zero grade on the
project. You must submit your work to Journals on blackboard by due date. 1} Write a query to list all the employees; 2] Write a query to show the total value of all the orders that customers living in San lose or
Turlock have placed; 3} Wflte a query to show the books that are priced between 49.99 and 1:10.90; ti] l:iret the names of cities of customers who have placed an orderthrough employees who has sold the book "Quidtbooks for Dummies". Required: you must complete this query with sub
select. 5} Write a query to show all the employees whose names contains two letters “e”; 6] Write a query to find total 9 value in each order. You shall display “one" and "Total”. Hint: y_ou
need to use "Group Byjf. ii Write a query to show the pairs of the names of the different customers who have purchased
the same booklsl; e 3] Write a query to show the book title and price for the book with the highest price; 91 1Ii'tlrite a query to show order number and the number of book titles [you may use ban] in each
order. For example, in order 1029 there are 4 book titles; 10] This query does not use the tables provided. Suppose that there was a hit‘and-run car accident.
No one remembered the full license plate of the run—away vehicle. However. the collected
evidence shows that the vehicle’s color is white, the make is BMW, and some letters on the
plate were remembered: there are three letter "C”s on the plate. Assume: a. the DMy’s database has a table called License which stores the driverfvehicle
information registered. This table has the following attributes: Driver_|D,
Lioense_plate_number, 'v‘e hicle_m ake. lvla ke_yea r, etc. b. Another table is called Drivers. which stores the driver information which has the
following attributes: Driver_ID. Driver_name, Address, phone. etc. Required: - Create the tables on the server using the phpmyadmin. Insert at least 10 entries for
—Efl£hlahlfl— - Write a query to show all driver names and License_plate_number ,i lv'ehicle_mal:e f
lvehicle year that may contain the vehicle information provided by the witness. Add
attributes it necessary to complete the assignment. 11} {bonus} Based on query #9, find the order number and the number of book titles, total number
of books, and average book {unit} price for each order.

ATTACHMENT PREVIEW Download attachment

table.jpg