Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables:
OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)
OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID)
GG_SERVICE (ServiceID, ServiceDescription, CostPerHour);
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
PROPERTY_SERVICE ( PropertyServiceID , PropertyID , ServiceID, ServiceDate , EmployeeID, HoursWorked)
The referential integrity constraints are:
OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER
PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_PROPERTY
ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE
EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE
Assume that OwnerID in OWNER, PropertyID in PROPERTY, and EmployeeID in EMPLOYEE are surrogate keys with values as follows:
OwnerID Start at 1 Increment by 1
PropertyID Start at 1 Increment by 1
ServiceID Start at 1 Increment by 1
EmployeeID Start at 1 Increment by 1
PropertyServiceID Start at 1 Increment by 1
Sample data are shown in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42. OwnerType is either Individual or Corporation. PropertyType is one of Office, Apartments, or Private Residence. ExperienceLevel is one of Unknown, Junior, Senior, Master or SuperMaster. These tables, referential integrity constraints, and data are used
Sample Data for Garden Glory OWNER Table OwnerID OwnerName OwnerEmailAddress OwnerType
1 Mary Jones [email protected] Individual
2 DT Enterprises [email protected] Corporation
3 Sam Douglas [email protected] Individual
4 UNY Enterprises [email protected] Corporation
5 Doug Samuels [email protected] Individual
Sample Data for Garden Glory OWNED_PROPERTY Table PropertyID PropertyName PropertyType Street City State ZIP OwnerID
1 Eastlake Building Office 123 Eastlake Seattle WA 98119 2
2 Elm St Apts Apartments 4 East Elm Lynwood WA 98223 1
3 Jefferson Hill Office 42 West 7th St Bellevue WA 98007 2
4 Lake View Apts Apartments 1265 32nd Avenue Redmond WA 98052 3
5 Kodak Heights Apts Apartments 65 32nd Avenue Redmond WA 98052 4
6 Jones House Private Residence 1456 48th St Bellevue WA 98007 1
7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3
8 Samuels House Private Residence 567 151st St Redmond WA 98052 5
as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database GARDEN_GLORY. Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data. Write SQL statements and answer questions for this database as follows:
FIGURE 3-40
Sample Data for Garden Glory EMPLOYEE Table EmployeeID LastName FirstName CellPhone ExperienceLevel
1 Smith Sam 206-254-1234 Master
2 Evanston John 206-254-2345 Senior
3 Murray Dale 206-254-3456 Junior
4 Murphy Jerry 585-545-8765 Master
5 Fontaine Joan 206-254-4567 Senior
FIGURE 3-41
Sample Data for Garden Glory GG_SERVICE Table ServiceID ServiceDescription CostPerHour
1 Mow Lawn 25.00
2 Plant Annuals 25.00
3 Weed Garden 30.00
4 Trim Hedge 45.00
5 Prune Small Tree 60.00
6 Trim Medium Tree 100.00
7 Trim Large Tree 125.00
FIGURE 3-42
Sample Data for Garden Glory PROPERTY_SERVICE Table PropertyServiceID PropertyID ServiceID ServiceDate EmployeeID HoursWorked
1 1 2 2019-05-05 1 4.50
2 3 2 2019-05-08 3 4.50
3 2 1 2019-05-08 2 2.75
4 6 1 2019-05-10 5 2.50
5 5 4 2019-05-12 4 7.50
6 8 1 2019-05-15 4 2.75
7 4 4 2019-05-19 1 1.00
8 7 1 2019-05-21 2 2.50
9 6 3 2019-06-03 5 2.50
10 5 7 2019-06-08 4 10.50
11 8 3 2019-06-12 4 2.75
12 4 5 2019-06-15 1 5.00
13 7 3 2019-06-19 2 4.00
F. Write an SQL statement to list LastName, FirstName, and CellPhone for all employees having an experience level of Master and FirstName that begins with the letter J
will you please explain thank you
In: Computer Science
Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables:
OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)
OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID)
GG_SERVICE (ServiceID, ServiceDescription, CostPerHour);
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
PROPERTY_SERVICE ( PropertyServiceID , PropertyID , ServiceID, ServiceDate , EmployeeID, HoursWorked)
The referential integrity constraints are:
OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER
PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_PROPERTY
ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE
EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE
Assume that OwnerID in OWNER, PropertyID in PROPERTY, and EmployeeID in EMPLOYEE are surrogate keys with values as follows:
OwnerID Start at 1 Increment by 1
PropertyID Start at 1 Increment by 1
ServiceID Start at 1 Increment by 1
EmployeeID Start at 1 Increment by 1
PropertyServiceID Start at 1 Increment by 1
Sample data are shown in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42. OwnerType is either Individual or Corporation. PropertyType is one of Office, Apartments, or Private Residence. ExperienceLevel is one of Unknown, Junior, Senior, Master or SuperMaster. These tables, referential integrity constraints, and data are used
Sample Data for Garden Glory OWNER Table OwnerID OwnerName OwnerEmailAddress OwnerType
1 Mary Jones [email protected] Individual
2 DT Enterprises [email protected] Corporation
3 Sam Douglas [email protected] Individual
4 UNY Enterprises [email protected] Corporation
5 Doug Samuels [email protected] Individual
Sample Data for Garden Glory OWNED_PROPERTY Table PropertyID PropertyName PropertyType Street City State ZIP OwnerID
1 Eastlake Building Office 123 Eastlake Seattle WA 98119 2
2 Elm St Apts Apartments 4 East Elm Lynwood WA 98223 1
3 Jefferson Hill Office 42 West 7th St Bellevue WA 98007 2
4 Lake View Apts Apartments 1265 32nd Avenue Redmond WA 98052 3
5 Kodak Heights Apts Apartments 65 32nd Avenue Redmond WA 98052 4
6 Jones House Private Residence 1456 48th St Bellevue WA 98007 1
7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3
8 Samuels House Private Residence 567 151st St Redmond WA 98052 5
as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database GARDEN_GLORY. Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data. Write SQL statements and answer questions for this database as follows:
Will you please explain and write the instructions how to write on mySQL and thank you
FIGURE 3-40
Sample Data for Garden Glory EMPLOYEE Table EmployeeID LastName FirstName CellPhone ExperienceLevel
1 Smith Sam 206-254-1234 Master
2 Evanston John 206-254-2345 Senior
3 Murray Dale 206-254-3456 Junior
4 Murphy Jerry 585-545-8765 Master
5 Fontaine Joan 206-254-4567 Senior
FIGURE 3-41
Sample Data for Garden Glory GG_SERVICE Table ServiceID ServiceDescription CostPerHour
1 Mow Lawn 25.00
2 Plant Annuals 25.00
3 Weed Garden 30.00
4 Trim Hedge 45.00
5 Prune Small Tree 60.00
6 Trim Medium Tree 100.00
7 Trim Large Tree 125.00
FIGURE 3-42
Sample Data for Garden Glory PROPERTY_SERVICE Table PropertyServiceID PropertyID ServiceID ServiceDate EmployeeID HoursWorked
1 1 2 2019-05-05 1 4.50
2 3 2 2019-05-08 3 4.50
3 2 1 2019-05-08 2 2.75
4 6 1 2019-05-10 5 2.50
5 5 4 2019-05-12 4 7.50
6 8 1 2019-05-15 4 2.75
7 4 4 2019-05-19 1 1.00
8 7 1 2019-05-21 2 2.50
9 6 3 2019-06-03 5 2.50
10 5 7 2019-06-08 4 10.50
11 8 3 2019-06-12 4 2.75
12 4 5 2019-06-15 1 5.00
13 7 3 2019-06-19 2 4.00
B. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading updates and deletions and justify those assumptions. (Hint: You can combine the SQL for your answers to parts A and B.)
C. Write SQL statements to insert the data into each of the five Garden Glory database tables. Assume that any surrogate key value will be supplied by the DBMS. Use the data in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42.
will you please explain thank you
In: Computer Science
Actual and Potential GDP (a FRED question): Using the FRED database, locate the Congressional Budget Office’s measure of potential GDP by searching for ”GDPPOT”. Using the ”Add Data Series” option, add the series ”Real Gross Domestic Product (GDP1)” to this graph. Adjust the sliders to show the data from around 1985 to the present.
(1) Display the graph.
(2) Download a graph of the ”investment share of GDP” (for gross private domestic invest- ment) and display those data graphically since 1990.
(3) How does this second graph help you understand the first graph in terms of the data since the Great Recession?
In: Economics
A loan company wants to design a database to track student loans. Each student attending school is eligible for a loan. A student may have more than one loan. A student may be registered, possibly at different times, in more than one school. Each loan should belong to only one bank. Each bank can approve as many loans as it desires. For each loan, the loan company will track: the student’s SSN, name, address, amount of loan, date of the loan, interest rate ( which may be different for each loan as determined by the bank), duration of the loan, monthly payment, remaining balance, school ID, school name and address, number of years the student has been at the school, bank name, bank branch, and bank ID. Draw an ER diagram. State any assumptions you make in the diagram
In: Computer Science
Pretend that I (your professor) have enough free time to create an elaborate database for one of my fall face-to-face classes that contained the following information:
1) daily attendance
2) grades for every assignment and exam (including date of assignment)
3) age of students
4) every contact that each student made with me including email and office visit
5) voluntary class participation per student
6) whether students were freshmen, sophomore, junior, or senior
7) how many hours each student worked at a job each week
Write a 300-word essay discussing what ways I (the professor) could examine the relationship of this data to learn more about this class. Also, is there any additional data that would be interesting to have to examine this class?
Example: "I would like to analyze the relationship between class attendance and grade. I suspect that, on average, the students who do not show up to class are less likely to perform well..."
In: Computer Science
You have information about recent sales that you want to use for testing the database. 1. Create the tables for the data provided. The tables should include the primary and foreign keys. Provide the SQL statements. 2. Insert the data into the tables. Provide the SQL statements. 3. Show the contents of each table. Provide the SQL statements.
Customer Table Customer ID, Last Name, First Name, Street Address, City, State, Zip Code, Current Balance, Credit Limit, Sales Rep. ID Sales Representatives Table Sales Rep ID, Last Name, First Name, Street Address, City, State, Zip, Region, Region Description, Total Commission, Commission Rate Orders Table Order ID, Order Date, Customer, Shipping Date, Order Lines Order ID, Part ID, Number Ordered, Quoted Price Part Table Part ID, Part Description, Units on Hand, Class, Warehouse Number, Unit Price
Please show all work
In: Computer Science
Utilize the Outdoor Clubs & Product database to create a PL/SQL anonymous block program unit that lists the product_name attribute value and a demand status text display. The demand status text display is determined by counting the number of times a product has been ordered so far. The demand status text display will be either “Low Demand” or “High Demand”. Low Demand is displayed if the product has been ordered less than 2 times. High Demand is displayed if the product has been ordered more than 2 times. Save the PL/SQL anonymous block program unit as a script file. Due to the business rule of not carrying products that have low demand, reduce the price of products (by $1) that have low demand.
--- Database Tables --- _
id varchar2(4) constraint supplier_pk primary key, name varchar2(30), street varchar2(30), city varchar2(15), state char(2), zip number(5), phone varchar2(10)); create table product (product_id number(5) constraint product_pk primary key, product_name varchar2(30), quantity_in_stock number(3), reorder_point number(2), price number(5,2), supplier_id varchar2(4) constraint product_fk references supplier, reorder_qty number(2)); create table purchase_order (po_no varchar2(4) constraint purchase_order_pk primary key, po_date date, product_id number(5) constraint purchase_order_fk1 references product, quantity number(3), supplier_id varchar2(4) constraint purchase_order_fk2 references supplier); create table customer (customer_id number(3) constraint customer_pk primary key, first_name varchar2(10), last_name varchar2(10), street varchar2(30), city varchar2(15), state char(2)default 'MO', zip number(5), phone varchar2(10)); create table club_membership (membership_id number(5) constraint club_membership_pk primary key, membership_date date, duration number(2), amount number(4), payment_type varchar2(5)constraint membership_payment_type_ck check ((payment_type = 'CC') or (payment_type = 'Check')), club_id number(3) constraint club_membership_fk1 references sporting_clubs, customer_id number(3) constraint club_membership_fk2 references customer); create table product_order (order_id number(4) constraint product_order_pk primary key, order_date date, ship_date date, payment_type varchar2(5)constraint prod_order_payment_type_ck check ((payment_type = 'CC') or (payment_type = 'Check')), total number (6,2), customer_id number(3) constraint product_order_fk1 references customer); create table order_details (order_id number(4), product_id number(5), quantity number(2), constraint order_details_pk primary key (order_id,product_id), constraint order_details_fk1 foreign key (order_id) references product_order, constraint order_details_fk2 foreign key (product_id) references product); create sequence club_sequence start with 100 increment by 10 nocache; insert into sporting_clubs values(club_sequence.nextval, 'Hillside Mountain Club', '1 Winona St','Wichita','KS',34342,'3163997676'); insert into sporting_clubs values(club_sequence.nextval, 'Branson Climbing Club', '2 Sherwood Dr.','Branson','MO',65670,'4174485676'); insert into sporting_clubs values(club_sequence.nextval, 'Cherokee Rafting Club', '44 Kent Ave.','St. Charles','MO',66572,'3147780870'); insert into sporting_clubs values(club_sequence.nextval, 'White Plains Club', '225 Tracy St.','New York','NY',13567,'2126678090'); insert into club_activity values(100,'Hiking'); insert into club_activity values(100,'Climbing'); insert into club_activity values(100,'Walking'); insert into club_activity values(110,'Hiking'); insert into club_activity values(110,'Climbing'); insert into club_activity values(110,'Conservation'); insert into club_activity values(110,'Walking'); insert into club_activity values(120,'Conservation'); insert into club_activity values(120,'Canoeing'); insert into club_activity values(130,'Conservation'); insert into club_activity values(130,'Canoeing'); insert into club_activity values(130,'Walking'); create sequence supplier_sequence start with 500 increment by 10 nocache; insert into supplier values('S'||supplier_sequence.nextval,'Hillside Ski','2717 S. Western Ave.','Los Angeles','CA',90006,'7146654959'); insert into supplier values('S'||supplier_sequence.nextval,'Tiger Mountain','2600 S. Vermont Ave.','Los Angeles','CA',90006,'7143327878'); insert into supplier values('S'||supplier_sequence.nextval,'Asha Outdoor','44 S. LaSalle St.','Chicago','IL',60603,'3125554678'); insert into supplier values('S'||supplier_sequence.nextval,'Sheraton Recreation','225 Tracy St.','New York','NY',13567,'2128889569'); create sequence product_id_sequence start with 10010 increment by 1 nocache; insert into product values(product_id_sequence.nextval,'Beginner''s Ski Boot',20,5,9.75,'S500',25); insert into product values(product_id_sequence.nextval,'Intermediate Ski Boot',18,5,12.99,'S500',20); insert into product values(product_id_sequence.nextval,'Pro Ski Boot',21,7,15.49,'S510',25); insert into product values(product_id_sequence.nextval,'Beginner''s Ski Pole',15,3,25.49,'S500',20); insert into product values(product_id_sequence.nextval,'Intermediate Ski Pole',20,3,29.99,'S520',22); insert into product values(product_id_sequence.nextval,'Pro Ski Pole',21,5,34.99,'S530',25); insert into product values(product_id_sequence.nextval,'Road Bicycle',15,4,34.95,'S520',18); insert into product values(product_id_sequence.nextval,'Mountain Bicycle',19,4,49.99,'S520',20); insert into product values(product_id_sequence.nextval,'Tire Pump',8,2,7.99,'S530',10); insert into product values(product_id_sequence.nextval,'Water Bottle',25,4,2.49,'S510',25); insert into product values(product_id_sequence.nextval,'Bicycle Tires',30,5,4.99,'S500',33); insert into product values(product_id_sequence.nextval,'Bicycle Helmet',23,6,10.95,'S510',25); create sequence po_sequence start with 11 nocache; insert into purchase_order values('PO'||po_sequence.nextval,to_date('5/25/12','mm/dd/yy'),10011,20,'S500'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('5/12/12','mm/dd/yy'),10015,25,'S530'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('6/25/12','mm/dd/yy'),10011,20,'S500'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('6/15/12','mm/dd/yy'),10018,10,'S530'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('7/10/12','mm/dd/yy'),10015,25,'S530'); insert into purchase_order values('PO'||po_sequence.nextval,to_date('7/25/12','mm/dd/yy'),10019,25,'S510'); create sequence customer_sequence start with 101 nocache; insert into customer values(customer_sequence.nextval,'Jack','Russell','25 North Madison Ave.','Springfield','MO',65807,'4178823434'); insert into customer values(customer_sequence.nextval,'Betty','Trumbell','550 South Court Dr.','St. Louis','MO',63140,'3125556670'); insert into customer values(customer_sequence.nextval,'Anil','Kaul','400 South Circle St.','Kansas City','MO',64530,'4316667070'); insert into customer values(customer_sequence.nextval,'Tom','Wiley','1500 North Grand St.','Springfield','MO',65810,'4178825560'); insert into customer values(customer_sequence.nextval,'Sharon','Stone','200 West Wagner St.','Springfield','MO',65807,'4176668890'); create sequence membership_sequence start with 10010 increment by 10 nocache; insert into club_membership values(membership_sequence.nextval,to_date('6/12/12','mm/dd/yy'),4,200,'CC',100,101); insert into club_membership values(membership_sequence.nextval,to_date('6/15/12','mm/dd/yy'),2,100,'Check',110,102); insert into club_membership values(membership_sequence.nextval,to_date('6/21/12','mm/dd/yy'),5,250,'Check',120,103); create sequence product_order_sequence start with 1001 nocache; insert into product_order values(product_order_sequence.nextval,to_date('5/27/12','mm/dd/yy'),to_date('6/1/12','mm/dd/yy'),'CC',134.95,102); insert into product_order values(product_order_sequence.nextval,to_date('5/28/12','mm/dd/yy'),to_date('6/2/12','mm/dd/yy'),'CC',134.85,103); insert into product_order values(product_order_sequence.nextval,to_date('5/28/12','mm/dd/yy'),to_date('6/3/12','mm/dd/yy'),'Check',12.45,104); insert into product_order values(product_order_sequence.nextval,to_date('6/5/12','mm/dd/yy'),to_date('6/10/12','mm/dd/yy'),'CC',44.43,105); insert into product_order values(product_order_sequence.nextval,to_date('6/6/12','mm/dd/yy'),to_date('6/8/12','mm/dd/yy'),'Check',52.48,103); insert into product_order values(product_order_sequence.nextval,to_date('6/8/12','mm/dd/yy'),to_date('6/12/12','mm/dd/yy'),'CC',131.94,104); insert into order_details values(1001,10011,2); insert into order_details values(1001,10015,3); insert into order_details values(1002,10011,5); insert into order_details values(1002,10016,2); insert into order_details values(1003,10019,5); insert into order_details values(1004,10018,3); insert into order_details values(1004,10011,1); insert into order_details values(1004,10019,3); insert into order_details values(1005,10017,1); insert into order_details values(1005,10019,1); insert into order_details values(1005,10021,1); insert into order_details values(1006,10012,4); insert into order_details values(1006,10015,2); commit;
In: Computer Science
Using the Class database:
***********************************************************************************************************************************************
***********************************************************************************************************************************************
***********************************************************************************************************************************************
#---Create and open the database
drop database if exists Class;
CREATE DATABASE Class;
#-- Using the database
USE Class;
# create student table
DROP TABLE IF EXISTS student;
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
gender ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
);
# create grade event table
DROP TABLE IF EXISTS grade_event;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
);
# create score table
# The PRIMARY KEY comprises two columns to prevent any
combination
# of event_id/student_id from appearing more than once.
DROP TABLE IF EXISTS score;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
PRIMARY KEY (event_id, student_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
);
# create absence table
DROP TABLE IF EXISTS absence;
CREATE TABLE absence
(
student_id INT UNSIGNED NOT NULL,
date DATE NOT NULL,
PRIMARY KEY (student_id, date),
FOREIGN KEY (student_id) REFERENCES student (student_id)
);
#--Populate the student table
INSERT INTO student VALUES ('Megan','F',NULL);
INSERT INTO student VALUES ('Joseph','M',NULL);
INSERT INTO student VALUES ('Kyle','M',NULL);
INSERT INTO student VALUES ('Katie','F',NULL);
INSERT INTO student VALUES ('Abby','F',NULL);
INSERT INTO student VALUES ('Nathan','M',NULL);
INSERT INTO student VALUES ('Liesl','F',NULL);
INSERT INTO student VALUES ('Ian','M',NULL);
INSERT INTO student VALUES ('Colin','M',NULL);
INSERT INTO student VALUES ('Peter','M',NULL);
INSERT INTO student VALUES ('Michael','M',NULL);
INSERT INTO student VALUES ('Thomas','M',NULL);
INSERT INTO student VALUES ('Devri','F',NULL);
INSERT INTO student VALUES ('Ben','M',NULL);
INSERT INTO student VALUES ('Aubrey','F',NULL);
INSERT INTO student VALUES ('Rebecca','F',NULL);
INSERT INTO student VALUES ('Will','M',NULL);
INSERT INTO student VALUES ('Max','M',NULL);
INSERT INTO student VALUES ('Rianne','F',NULL);
INSERT INTO student VALUES ('Avery','F',NULL);
INSERT INTO student VALUES ('Lauren','F',NULL);
INSERT INTO student VALUES ('Becca','F',NULL);
INSERT INTO student VALUES ('Gregory','M',NULL);
INSERT INTO student VALUES ('Sarah','F',NULL);
INSERT INTO student VALUES ('Robbie','M',NULL);
INSERT INTO student VALUES ('Keaton','M',NULL);
INSERT INTO student VALUES ('Carter','M',NULL);
INSERT INTO student VALUES ('Teddy','M',NULL);
INSERT INTO student VALUES ('Gabrielle','F',NULL);
INSERT INTO student VALUES ('Grace','F',NULL);
INSERT INTO student VALUES ('Emily','F',NULL);
#--Populate grade event table
INSERT INTO grade_event VALUES('2015-09-03', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-09-06', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-09-09', 'T', NULL);
INSERT INTO grade_event VALUES('
2015-09-16', 'Q', NULL);
INSERT INTO grade_event VALUES(
'2015-09-23', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-10-01', 'T', NULL);
#--Populate the score table
INSERT INTO score VALUES (1,1,20);
INSERT INTO score VALUES (3,1,20);
INSERT INTO score VALUES (4,1,18);
INSERT INTO score VALUES (5,1,13);
INSERT INTO score VALUES (6,1,18);
INSERT INTO score VALUES (7,1,14);
INSERT INTO score VALUES (8,1,14);
INSERT INTO score VALUES (9,1,11);
INSERT INTO score VALUES (10,1,19);
INSERT INTO score VALUES (11,1,18);
INSERT INTO score VALUES (12,1,19);
INSERT INTO score VALUES (14,1,11);
INSERT INTO score VALUES (15,1,20);
INSERT INTO score VALUES (16,1,18);
INSERT INTO score VALUES (17,1,9);
INSERT INTO score VALUES (18,1,20);
INSERT INTO score VALUES (19,1,9);
INSERT INTO score VALUES (20,1,9);
INSERT INTO score VALUES (21,1,13);
INSERT INTO score VALUES (22,1,13);
INSERT INTO score VALUES (23,1,16);
INSERT INTO score VALUES (24,1,11);
INSERT INTO score VALUES (25,1,19);
INSERT INTO score VALUES (26,1,10);
INSERT INTO score VALUES (27,1,15);
INSERT INTO score VALUES (28,1,15);
INSERT INTO score VALUES (29,1,19);
INSERT INTO score VALUES (30,1,17);
INSERT INTO score VALUES (31,1,11);
INSERT INTO score VALUES (1,2,17);
INSERT INTO score VALUES (2,2,8);
INSERT INTO score VALUES (3,2,13);
INSERT INTO score VALUES (4,2,13);
INSERT INTO score VALUES (5,2,17);
INSERT INTO score VALUES (6,2,13);
INSERT INTO score VALUES (7,2,17);
INSERT INTO score VALUES (8,2,8);
INSERT INTO score VALUES (9,2,19);
INSERT INTO score VALUES (10,2,18);
INSERT INTO score VALUES (11,2,15);
INSERT INTO score VALUES (12,2,19);
INSERT INTO score VALUES (13,2,18);
INSERT INTO score VALUES (14,2,18);
INSERT INTO score VALUES (15,2,16);
INSERT INTO score VALUES (16,2,9);
INSERT INTO score VALUES (17,2,13);
INSERT INTO score VALUES (18,2,9);
INSERT INTO score VALUES (19,2,11);
INSERT INTO score VALUES (21,2,12);
INSERT INTO score VALUES (22,2,10);
INSERT INTO score VALUES (23,2,17);
INSERT INTO score VALUES (24,2,19);
INSERT INTO score VALUES (25,2,10);
INSERT INTO score VALUES (26,2,18);
INSERT INTO score VALUES (27,2,8);
INSERT INTO score VALUES (28,2,13);
INSERT INTO score VALUES (29,2,16);
INSERT INTO score VALUES (30,2,12);
INSERT INTO score VALUES (31,2,19);
INSERT INTO score VALUES (1,3,88);
INSERT INTO score VALUES (2,3,84);
INSERT INTO score VALUES (3,3,69);
INSERT INTO score VALUES (4,3,71);
INSERT INTO score VALUES (5,3,97);
INSERT INTO score VALUES (6,3,83);
INSERT INTO score VALUES (7,3,88);
INSERT INTO score VALUES (8,3,75);
INSERT INTO score VALUES (9,3,83);
INSERT INTO score VALUES (10,3,72);
INSERT INTO score VALUES (11,3,74);
INSERT INTO score VALUES (12,3,77);
INSERT INTO score VALUES (13,3,67);
INSERT INTO score VALUES (14,3,68);
INSERT INTO score VALUES (15,3,75);
INSERT INTO score VALUES (16,3,60);
INSERT INTO score VALUES (17,3,79);
INSERT INTO score VALUES (18,3,96);
INSERT INTO score VALUES (19,3,79);
INSERT INTO score VALUES (20,3,76);
INSERT INTO score VALUES (21,3,91);
INSERT INTO score VALUES (22,3,81);
INSERT INTO score VALUES (23,3,81);
INSERT INTO score VALUES (24,3,62);
INSERT INTO score VALUES (25,3,79);
INSERT INTO score VALUES (26,3,86);
INSERT INTO score VALUES (27,3,90);
INSERT INTO score VALUES (28,3,68);
INSERT INTO score VALUES (29,3,66);
INSERT INTO score VALUES (30,3,79);
INSERT INTO score VALUES (31,3,81);
INSERT INTO score VALUES (2,4,7);
INSERT INTO score VALUES (3,4,17);
INSERT INTO score VALUES (4,4,16);
INSERT INTO score VALUES (5,4,20);
INSERT INTO score VALUES (6,4,9);
INSERT INTO score VALUES (7,4,19);
INSERT INTO score VALUES (8,4,12);
INSERT INTO score VALUES (9,4,17);
INSERT INTO score VALUES (10,4,12);
INSERT INTO score VALUES (11,4,16);
INSERT INTO score VALUES (12,4,13);
INSERT INTO score VALUES (13,4,8);
INSERT INTO score VALUES (14,4,11);
INSERT INTO score VALUES (15,4,9);
INSERT INTO score VALUES (16,4,20);
INSERT INTO score VALUES (18,4,11);
INSERT INTO score VALUES (19,4,15);
INSERT INTO score VALUES (20,4,17);
INSERT INTO score VALUES (21,4,13);
INSERT INTO score VALUES (22,4,20);
INSERT INTO score VALUES (23,4,13);
INSERT INTO score VALUES (24,4,12);
INSERT INTO score VALUES (25,4,10);
INSERT INTO score VALUES (26,4,15);
INSERT INTO score VALUES (28,4,17);
INSERT INTO score VALUES (30,4,11);
INSERT INTO score VALUES (31,4,19);
INSERT INTO score VALUES (1,5,15);
INSERT INTO score VALUES (2,5,12);
INSERT INTO score VALUES (3,5,11);
INSERT INTO score VALUES (5,5,13);
INSERT INTO score VALUES (6,5,18);
INSERT INTO score VALUES (7,5,14);
INSERT INTO score VALUES (8,5,18);
INSERT INTO score VALUES (9,5,13);
INSERT INTO score VALUES (10,5,14);
INSERT INTO score VALUES (11,5,18);
INSERT INTO score VALUES (12,5,8);
INSERT INTO score VALUES (13,5,8);
INSERT INTO score VALUES (14,5,16);
INSERT INTO score VALUES (15,5,13);
INSERT INTO score VALUES (16,5,15);
INSERT INTO score VALUES (17,5,11);
INSERT INTO score VALUES (18,5,18);
INSERT INTO score VALUES (19,5,18);
INSERT INTO score VALUES (20,5,14);
INSERT INTO score VALUES (21,5,17);
INSERT INTO score VALUES (22,5,17);
INSERT INTO score VALUES (23,5,15);
INSERT INTO score VALUES (25,5,14);
INSERT INTO score VALUES (26,5,8);
INSERT INTO score VALUES (28,5,20);
INSERT INTO score VALUES (29,5,16);
INSERT INTO score VALUES (31,5,9);
INSERT INTO score VALUES (1,6,100);
INSERT INTO score VALUES (2,6,91);
INSERT INTO score VALUES (3,6,94);
INSERT INTO score VALUES (4,6,74);
INSERT INTO score VALUES (5,6,97);
INSERT INTO score VALUES (6,6,89);
INSERT INTO score VALUES (7,6,76);
INSERT INTO score VALUES (8,6,65);
INSERT INTO score VALUES (9,6,73);
INSERT INTO score VALUES (10,6,63);
INSERT INTO score VALUES (11,6,98);
INSERT INTO score VALUES (12,6,75);
INSERT INTO score VALUES (14,6,77);
INSERT INTO score VALUES (15,6,62);
INSERT INTO score VALUES (16,6,98);
INSERT INTO score VALUES (17,6,94);
INSERT INTO score VALUES (18,6,94);
INSERT INTO score VALUES (19,6,74);
INSERT INTO score VALUES (20,6,62);
INSERT INTO score VALUES (21,6,73);
INSERT INTO score VALUES (22,6,95);
INSERT INTO score VALUES (24,6,68);
INSERT INTO score VALUES (25,6,85);
INSERT INTO score VALUES (26,6,91);
INSERT INTO score VALUES (27,6,70);
INSERT INTO score VALUES (28,6,77);
INSERT INTO score VALUES (29,6,66);
INSERT INTO score VALUES (30,6,68);
INSERT INTO score VALUES (31,6,76);
#--Populate the absence table
INSERT INTO `absence` VALUES (3,'2015-09-03');
INSERT INTO `absence` VALUES (5,'2015-09-03');
INSERT INTO `absence` VALUES (10,'2015-09-06');
INSERT INTO `absence` VALUES (10,'2015-09-09');
INSERT INTO `absence` VALUES (17,'2015-09-07');
INSERT INTO `absence` VALUES (20,'2015-09-07');
In: Computer Science
Please create the SQL queries using the lryics database under question 4 and use "select * from..." after each query to show the effects of your data manipulation query. Thanks for help
1. The title 'Time Flies' now has a new track, the 11th track 'Spring', which is 150 seconds long and has only a MP3 file. Insert the new track into Tracks table (Don’t hand-code any data for insert that can be looked up from the Titles table).
2. Create a new table called Members2 with the same fields as the Members table. (Use DESCRIBE to check if Members2 table is created).
3. Populate Members2 with the content of the Members table.
4. The area code for Columbus, Ohio (OH) has been changed from 277 to 899. Update the homephone and workphone numbers of all members in Members2 table accordingly.
DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;
CREATE TABLE Artists (
ArtistID int,
ArtistName varchar (50) NOT NULL ,
City varchar (25) NULL ,
Region varchar (15) NULL ,
Country varchar (20) NULL ,
WebAddress varchar (40) NULL ,
EntryDate date NULL ,
LeadSource varchar (10) NULL
);
Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');
CREATE TABLE Genre (
Genre varchar (15)
);
Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');
CREATE TABLE Members (
MemberID int ,
FirstName varchar (25) NULL ,
LastName varchar (25) NULL ,
Address varchar (60) NULL ,
City varchar (25) NULL ,
Region varchar (15) NULL ,
PostalCode varchar (10) NULL ,
Country varchar (20) NULL ,
HomePhone varchar (16) NULL ,
WorkPhone varchar (16) NULL ,
EMail varchar (40) NULL ,
Gender char (1) NULL ,
Birthday date NULL ,
SalesID smallint NULL
);
Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);
CREATE TABLE SalesPeople (
SalesID smallint ,
FirstName varchar (20) NOT NULL ,
LastName varchar (20) NOT NULL ,
Initials varchar (3) NULL ,
Base decimal(5,2) NULL,
Supervisor smallint NUll
);
Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);
CREATE TABLE Studios (
StudioID int,
StudioName varchar (40) NULL ,
Address varchar (60) NULL ,
City varchar (25) NULL ,
Region varchar (15) NULL ,
PostalCode varchar (10) NULL ,
Country varchar (20) NULL ,
WebAddress varchar (40) NULL ,
Contact varchar (50) NULL ,
EMail varchar (40) NULL ,
Phone varchar (16) NULL ,
SalesID smallint NULL
);
Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);
CREATE TABLE Titles (
TitleID int ,
ArtistID int NULL ,
Title varchar (50) NULL ,
StudioID int NULL ,
UPC varchar (13) NULL ,
Genre varchar (15) NULL
);
Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');
CREATE TABLE Tracks (
TitleID int NOT NULL ,
TrackNum smallint NOT NULL ,
TrackTitle varchar (50) NULL ,
LengthSeconds smallint NULL ,
MP3 smallint NULL ,
RealAud smallint NULL
);
Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);
CREATE TABLE XrefArtistsMembers (
MemberID int NOT NULL ,
ArtistID int NOT NULL ,
RespParty smallint NOT NULL
);
Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);
show tables;In: Computer Science
For Problems 1–4, using the STORES Database, formulate SQL queries, Hand in a listing of each query and its output.
1. (10 Points) For each customer, list each stock item ordered, the manufacturer, the quantity ordered, and the total price paid. Include the following columns in the order given below:
From Customer Table: Company
From Stock Table: Description
From the Manufact Table: Manu_Name
From the Items Table: Quantity, Total Price
Order the output by Company and Description.
2. (10 Points) List all orders with a shipping date between December 25, 1999 and January 5, 2000. List the Order Number, Order Date, Customer company name, and Shipping Date. Order by Customer Company Name and Order Number.
3. (10 Points) Count the number of customers who do not have any orders placed.
4. (10 Points) List all customers who are ordering equipment whose description begins with ‘tennis’ or ‘volleyball’. List the Customer number, Stock number, and Description. Do not repeat any rows.
5. (15 Points) Use the following SQL CREATE commands to CREATE the following tables in your
User ID:
CREATE TABLE Professor
(Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
Prof_Lname VARCHAR2(15) NOT NULL,
Prof_Hiredate DATE,
Prof_Sal NUMBER(8,2),
Prof_Dept CHAR(6)
);
CREATE TABLE Student
(Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
Stu_Lname VARCHAR2(15) NOT NULL,
Stu_Major CHAR(6),
Stu_CredHrs NUMBER(4),
Stu_GradePts NUMBER(5),
Prof_ID NUMBER(3),
CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
REFERENCES Professor
);
Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables.
6. (15 Points) Insert the following data into the tables created above using SQL INSERT commands.
Professor Table:
|
Prof_ID |
Prof_Lname |
Prof_Hiredate |
Prof_Sal |
Prof_Dept |
|
123 |
Hilbert |
20-MAY-1992 |
58000.00 |
MATH |
|
243 |
Newell |
15-JUL-1997 |
65500.00 |
CMPSCI |
|
389 |
Lessing |
04-APR-1988 |
40250.00 |
ENG |
Student Table:
|
Stu_ID |
Stu_Lname |
Stu_Major |
Stu_CredHrs |
Stu_GradePts |
Prof_ID |
|
2001 |
Parker |
CMPSCI |
52 |
160 |
243 |
|
2166 |
Smith |
ENG |
30 |
75 |
389 |
|
3200 |
Garcia |
MATH |
62 |
248 |
123 |
|
4520 |
Smith |
CMPSCI |
45 |
157 |
NULL |
In: Computer Science