Please help with questions 5 through 8. 1. Create a new table that will be used
ID: 670043 • Letter: P
Question
Please help with questions 5 through 8.
1. Create a new table that will be used to track employee location. The attributes associated with the table: employee id, location id, location description, street address, city, state, zipcode. Create a new table the attributes listed above. Please identify the primary key. It has a relationship with employees. Provide SQL statement below.
Employee Locataion:
Attributes: employee id,location id,location description,street address,cirt,state,zipcode.
SQL Statement to create table Employee Location:
Primary Key: employe_id
CREATE TABLE Employee_Loc(employee_id INT PRIMARY KEY,location_id INT,location VARCHAR(25),location_description VARCHAR(60),street_address VARCHAR(100),city VARCHAR(20),state VARCHAR(20),zipcode INT);
2. Add three additional columns to the Books table. The attributes are book ISBN, book secondary desc, and purchase price. Provide SQL statement below.
ALTER TABLE Books ADD COLUMN ISBN VARCHAR(20),
ADD COLUMN secondary_desc VARCHAR(30),
ADD COLUMN purchase_price DOUBLE ;
3. Create a query to select all data from the customers table Provide the SQL statement below:
SELECT * FROM customers;
4. Create a basic query that will extract the following information from the books, title id, title, publisher, pubdate, edition and cost. Order by publisher. Provide the SQL statement below:
SELECT title_id,title,publisher,pubdate,edition,cost FROM Books ORDER BY publisher;
5. Find all find all customer orders placed between January 2014 and June 2014. Provide the SQL statement below:
6. Create a new query to display all authors with last name of “Smith” and all books by this author. Show author id, author first name, author last name, title id, and title. Group data by title id. Provide the SQL statement below:
7. Update the new fields added tothe book table for title id 123. Please provide the SQL statement below.
8. Delete all orders placed before January 2011. Provide the SQL statement below.
Books Authors AuthorlD FirstName LastName YearBom Authors ConditionID ConditionName Description 0c AuthorlD TitleID Publisher Description ConditionlD 1 1 BooksOrders Positions PositionlD JobDescrip Customers CustomerlD FirstName LastName EmployeelD LastName Address1 Addres2 2 Address1 Addres2 DeliveryDate PaymentlD OrderStatus FormOfPayment PaymentID PaymentDescrip StatusDescipExplanation / Answer
====================================================================
5. Find all find all customer orders placed between January 2014 and June 2014. Provide the SQL statement below:
--------
Answer:
--------
SELECT c.CustomerID,c.FirstName,c.LastName
o.OrderID,o.OrderDate
FROM
Customers c, Orders o
WHERE
c.CustomerID=o.CustomerID
AND
o.OrderDate >= '2014-01-01'
AND
o.OrderDate >= '2014-06-30';
====================================================================
6. Create a new query to display all authors with last name of “Smith” and all books by this author. Show author id, author first name, author last name, title id, and title. Group data by title id. Provide the SQL statement below:
--------
Answer:
--------
SELECT a.AuthorID,a.FirstName,a.LastName,t.TitleID,t.Title
FROM
Authors a,BooksAuthors ba,Books b
WHERE
a.AuthorID =ba.AuthorID
AND
ba.TitleID =b.TitleID
AND
a.LastName = 'Smith'
GROUP BY
b.TitleID;
====================================================================
7. Update the new fields added tothe book table for title id 123. Please provide the SQL statement below.
--------
Answer:
--------
The new columns are added was as per the question are
ISBN
secondary_desc
purchase_price
UPDATE Books
SET ISBN='1239899',secondary_desc='MysqlBeginners',purchase_price=550.00
WHERE
TitleID=123;
====================================================================
8. Delete all orders placed before January 2011. Provide the SQL statement below.
--------
Answer:
--------
DELETE FROM Orders
WHERE OrderDate < '2011-01-01';
====================================================================