Space X Bank
CREATE TABLE Branch(BranchIDNumber VARCHAR(15) PRIMARY KEY NOT
NULL, BranchName VARCHAR(6) NOT NULL, Address...
Space X Bank
CREATE TABLE Branch(BranchIDNumber VARCHAR(15) PRIMARY KEY NOT
NULL, BranchName VARCHAR(6) NOT NULL, Address VARCHAR(50) NOT NULL,
City TEXT NULL,
State CHAR(2) NULL, ZipCode INT(11) NOT NULL, OfficeNum VARCHAR(15)
NOT NULL, FaxNum VARCHAR(15) NOT NULL);
CREATE TABLE Employee(EmployeeIDNumber VARCHAR(15) NOT
NULL,FirstName VARCHAR(35) NOT NULL, LastName VARCHAR(35) NOT
NULL,
Email VARCHAR(100) NOT NULL, BranchIDNumber VARCHAR(11) NOT NULL,
FOREIGN KEY(BranchIDNumber) REFERENCES
Branch(BranchIDNumber),
JobTitle ENUM("Manager","Staff") NOT NULL, Salary DECIMAL(8, 2) NOT
NULL, HomeNumber VARCHAR(13) NULL, CellNumber VARCHAR(13) NOT
NULL);
CREATE TABLE...
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...
If you had to create a new table in your database called
EMPLOYEE and load data...
If you had to create a new table in your database called
EMPLOYEE and load data into it using SQL commands below.
CREATE TABLE EMPLOYEE
(
EMP_ID INT PRIMARY KEY,
EMP_FNAME VARCHAR(200) NOT NULL,
EMP_LNAME VARCHAR(200) NOT NULL,
EMP_CITY VARCHAR(500) NULL ,
EMP_STATE VARCHAR(20) NULL
)
INSERT INTO EMPLOYEE VALUE (1, ‘Kevin’, ‘Lailnay’, NULL,
‘PA’)
INSERT INTO EMPLOYEE VALUE (2, ‘Sarita, ‘Kawn,
‘Princeton’,’NJ’)
INSERT INTO EMPLOYEE VALUE (2, ‘Gloria, ‘Pitts, ‘Allentown,
‘PA’)
INSERT INTO EMPLOYEE...
-- Table construction (just 1 simplest possible way)
CREATE TABLE PetType (
petTypeId VARCHAR(10) PRIMARY KEY,...
-- Table construction (just 1 simplest possible way)
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),
lastName VARCHAR(20) NOT NULL,
homePhoneNumber VARCHAR(20),
streetAddress VARCHAR(80),
suburb VARCHAR(20),
postcode VARCHAR(10)
);
CREATE TABLE Pet (
petId VARCHAR(10) PRIMARY KEY,
petName VARCHAR(20),
sex CHAR(1)
CHECK (sex IN ('M', 'F')),
petTypeId VARCHAR(10)
FOREIGN KEY REFERENCES PetType
);
CREATE TABLE PetAndOwner (
ownerId VARCHAR(10),
petId VARCHAR(10),
PRIMARY KEY (ownerId, petId),...
Create 2 stored procedures: sp_AddNewUser and sp_AddNewRole. Use
the ManyToMany script as your base table structures....
Create 2 stored procedures: sp_AddNewUser and sp_AddNewRole. Use
the ManyToMany script as your base table structures.
The stored procedure should accept the parameters needed to input
the data for each table.
NOTE: You do not need to input the UserID or RoleID. These are
surrogate keys and the system automatically inserts them when you
insert a row in the tables.
On execution, the stored procedure should check the database to see
if the user exists, if so, return a message...
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...
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...
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...
CREATE TABLE Customers
(Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
Address VARCHAR2(20),
City...
CREATE TABLE Customers
(Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
Address VARCHAR2(20),
City VARCHAR2(12),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4),
Region CHAR(2),
Email VARCHAR2(30),
CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#),
CONSTRAINT customers_region_ck
CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E'))
);
CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));
In...