Question

Using the Class database, answer the following questions: 1. Becca missed class on September 15, 2015....

Using the Class database, answer the following questions:

1. Becca missed class on September 15, 2015. Insert this absence into the absence table.

  1. Insert your snip of the construct used here.

b. Display the contents of the absence table. Be sure all rows are displayed.

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');

Engineering   Computer-Science   
0 0
Add a commentTranscribed image text
Next >

Homework Answers

Answer #1

#-- This command is used to find Becca's Student_id

select student_id from student where name= 'Becca';

#-- Inserting Becca's absence for 15th September,2015.

INSERT INTO absence Values (22 , '2015-09-15');

#-- Displaying the content of absence table

select * from absence;

-----------------------------------------------------------------------

Output of 1st command

22

Output of Last Command

3       2015-09-03
5       2015-09-03
10      2015-09-06
10      2015-09-09
17      2015-09-07
20      2015-09-07
22      2015-09-15
0 0
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment 4’s schema (Customer-Invoice-Line-Product-Vendor). Make sure that your SQL script runs without any errors. Submit your answers in a .SQL file. 1 (2 Points) - Find the count of distinctvendors thatsupplied products that are priced lowerthan 185? 2 (2 Points) - For each vendor, find their product that has the lowest product quantity. Your output should include vendor code, vendor name, product description and product...
SQL DATABASE Task 4 [1.5 marks] Create Index (0.5 marks) Currently, the database only contains a...
SQL DATABASE Task 4 [1.5 marks] Create Index (0.5 marks) Currently, the database only contains a small number of records. However, the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized. Write a command to create an index on student_name column of the student table. Create view – 1 mark Write a command to create a view to list the student ID and...
Find the total amount of ’Deposit’ transactions at the bank. Find the list of transactions (statement)...
Find the total amount of ’Deposit’ transactions at the bank. Find the list of transactions (statement) of September 2019 (09/01/2019 to 09/30/2019) for account ’1111222233331441’ (note: look at the date format). Find the balance of ’1111222233331441’ before 09/01/2019 ((not including 09/01/2019). Find the name of the customer that deposited the highest amount with one transaction (include the transaction amount). Bank.sql is under this statement. DROP DATABASE IF EXISTS Bank; CREATE DATABASE Bank; USE Bank; DROP TABLE IF EXISTS transaction; DROP...
For each account of the previous question, compute the Balance, and return a table that shows...
For each account of the previous question, compute the Balance, and return a table that shows the account number, type, and balance for each account (hint: use UNION). The list of customer names that have transactions greater than or equal to one thousand dollars. A) Answer this question using only nested queries (i.e., each select is over only one table). B) Answer this query using joins. Bank.sql is under this statement. DROP DATABASE IF EXISTS Bank; CREATE DATABASE Bank; USE...
Describe what the following query does using lyrics database with mysql. lyrics database is posted below...
Describe what the following query does using lyrics database with mysql. lyrics database is posted below 1.) select m.lastname, m.firstname, s.lastname from members m inner join salespeople s using (salesID) order by m.lastname asc; 2.) select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300 3.) SELECT artistName FROM Artists WHERE artistID IN (SELECT artistID FROM Titles) DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers; DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties; DROP...
Assignment Instructions (MY WORK IN MYSQLBENCH)- Follow instructions in class to download and build a database...
Assignment Instructions (MY WORK IN MYSQLBENCH)- Follow instructions in class to download and build a database in your DBMS. Write and demonstrate queries (in-class) that do the following: Download the appropriate attached SQL file for your DBMS. Load the file into your database design tool and run it to build the Pine Valley Furniture Company database. This is the database that is used in the textbook examples for both Chapters 5 and 6. Write a query to show the price...
1. For the Pet Database described in the previous practical, write an SQL statement with a...
1. For the Pet Database described in the previous practical, write an SQL statement with a subquery to list all the people in the Owner table who own more than one pet. should be a collum of the total number of pets owned, owner id, first name and last name -- DROP TABLE PetAndOwner, Pet, PetType, Owner; CREATE TABLE PetType ( petTypeId VARCHAR(10) PRIMARY KEY, animalType VARCHAR(20), breed VARCHAR(20) ); CREATE TABLE Owner ( ownerId VARCHAR(10) PRIMARY KEY, firstName VARCHAR(20),...
Find the total number of employees and the total number of dependents for every department (the...
Find the total number of employees and the total number of dependents for every department (the number of dependents for a department is the sum of the number of dependents for each employee working for that department). Return the result as department name, total number of employees, and total number of dependents Based on the following table: -- drop tables DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; DROP TABLE DEPARTMENT CASCADE CONSTRAINTS; DROP TABLE DEPT_LOCATIONS CASCADE CONSTRAINTS; DROP TABLE PROJECT CASCADE CONSTRAINTS;...
SQL Data: you can just copy paste it into mysql to. CREATE DATABASE University;USE University; CREATE...
SQL Data: you can just copy paste it into mysql to. CREATE DATABASE University;USE University; CREATE TABLE Student (  sid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  address VARCHAR(20) NOT NULL,  major CHAR(2)); CREATE TABLE Professor (  pid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  department VARCHAR(10) NOT NULL); CREATE TABLE Course (  cid INT PRIMARY KEY,  title VARCHAR(20) NOT NULL UNIQUE,  credits INT NOT NULL,  area VARCHAR(5) NOT NULL); CREATE TABLE Transcript (  sid INT,  cid INT,  pid INT,  semester VARCHAR(9),  year YEAR,  grade CHAR(1) NOT NULL,  PRIMARY KEY (sid, cid, semester, year),  FOREIGN KEY (sid) REFERENCES Student...
using mysql lyrics.database. i will provide the lyrics schema database info below 1. List the first...
using mysql lyrics.database. i will provide the lyrics schema database info below 1. List the first name, last name, and region of members who do not have an email. 2. List the first name, last name, and region of members who do not have an email and they either have a homephone ending with a 2 or a 3. 3. List the number of track titles that begin with the letter 's' and the average length of these tracks in...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT