Question

In: Computer Science

All questions in this assignment refer to the “om” database (or Schema) that you will find...

All questions in this assignment refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script.

Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. You are encouraged to test your SQL statements in Workbench, and please use the ‘tidy’ tool to properly format your SQL before you save it as a Script from Workbench or Notepad++. Use comments to indicate the question number for each answer. Please give all code. Don't give same like other code. Please do with error free.

1. Write a SELECT statement that returns the title, artist and unit_price columns from the items table. Return only those items that have a unit_price of at least $16, but less than $17.50

2. Write a SELECT statement to return all columns from the customers table where the customer’s state is Ohio (OH). Sort the results by last name in descending order.

3. Write a SELECT statement to return all columns from the customers table whose zip code begins with a “9”

4. Write a SELECT statement to return the order_id from the orders table where the number of days between the order_date and the shipped_date is less than 6

5. Expand the statement in question 4 to include the number of days between order and ship date as a column alias called ‘processing_days’ and sort the results by that column in ascending order

Solutions

Expert Solution

1.

SELECT title, artist, unit_price

FROM items

WHERE unit_price>= 16 AND unit_price<17;

2.

SELECT * FROM customers

WHERE state= 'OH'

ORDER BY last_name DESC;

3.

SELECT * FROM customers

WHERE zipcode LIKE '9%';

EXPLANATION:

Select * from tableName returns all the information in the table. But when WHERE clause is used, then it filters the rows according to the condition given.

LIKE operator is used to compare pattern where zipcode starts from 9.

4.

SELECT order_id

FROM orders

WHERE DATEDIFF(order_date, shipped_date) <6;

5.

SELECT order_id, DATEDIFF(order_date, shipped_date) AS "processing_days"

FROM orders

ORDER BY "processing_days" ASC;

EXPLANATION:

Selects order_id and number of days between order date and shipped.

DATEDIFF() is a function in MySQL to find out the difference in days between two intervals.

Keyword 'AS' is used to Rename the column name.

ORDER BY columnName ASC; is used to sort the results in the ascending order of columnName.


Related Solutions

This refer to the “om” database (or Schema) that you will find in your MySQL Workbench...
This refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script. Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. Please test your SQL statements in Workbench 1.       Using an INNER JOIN, select the order_id, order_date, shipped_date, fname, and customer_phone from the orders and customers tables. The fname is a...
This assignment refer to the “om” database (or Schema).      1.Write an INSERT statement that adds this...
This assignment refer to the “om” database (or Schema).      1.Write an INSERT statement that adds this row to the Items table:   a.       Artist:               Newly Added b.       Title:                      Assignment 3 c.        unit_price:         0.0 d.       ID:                          12 2.       Write an UPDATE statement that modifies the row you just added to the Items table. This statement should change the artist column to “RockOn”, and it should use the ID column to identify the row.    3.       Write a DELETE statement that deletes the row you...
[Q.4] Answer the following questions You are invited as a database architect to develop database schema...
[Q.4] Answer the following questions You are invited as a database architect to develop database schema for maintaining patient information for the NYU medical group (make necessary assumptions for the data requirements if needed). Each physician in the Lehman medical group is uniquely identified by physicianID o Each physician must have first name, and last name, and phone number Each patient is identified by patientID o Each patient must have first name, and last name, phone number, and insurance card...
We use the WMCRM database and here is the summary of the database schema (where schema...
We use the WMCRM database and here is the summary of the database schema (where schema is used in its meaning of a summary of the database structure): VEHICLE (InventoryID, Model, VIN) SALESPERSON (NickName, LastName, FirstName, HireDate, WageRate, CommissionRate, OfficePhone, EmailAddress, InventoryID) CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAddress, NickName) PHONE_NUMBER (CustomerID, PhoneNumber, PhoneType) CONTACT(ContactID, CustomerID,ContactDate,ContactType,Remarks) Where InventoryID in SALESPERSON must exist in InventoryID in VEHICLE NickName in CUSTOMER must exist in NickName in SALESPERSON CustomerID in PHONE_NUMBER...
in this assignment you will create and use a database to find meanings and synonyms for...
in this assignment you will create and use a database to find meanings and synonyms for given phrases. To do so you will use tables of synsets -- sets of one or more synonyms (specific phrases) that share the same meaning. Your program should: Display a message stating its goal Create a database file using SQLite using your name (i.e. use your name as a file name - for example, my database will be named "nimdvir") In your database, create...
The schema for the Academics database is as follows. Understanding this schema is necessary to answer...
The schema for the Academics database is as follows. Understanding this schema is necessary to answer the questions in Part B. DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) The semantics of most attributes are self-explanatory. For each relation, the primary key is underlined and any foreign keys are denoted by an asterisk (*). Some additional information for relations is given below: DEPARTMENT: Each academic department...
For the given database schema. Answer the following questions. Company Database customer(cust_id, name, address) product(product_id, product_name,...
For the given database schema. Answer the following questions. Company Database customer(cust_id, name, address) product(product_id, product_name, price, quantity) transaction(trans_id, cust_id, time_date) product_trans(product_id, trans_id) Identify the primary keys and foreign keys for the relations and specify at least two different types of integrity constraints that would be applicable for different relations given.
When is the conceptual schema of a database changed?- if we update data in the database...
When is the conceptual schema of a database changed?- if we update data in the database -if we alter the logical structure of the database -if we retrieve data from the database -if we alter the storage structure of the database Which of the following has the same meaning as the outer union? Group of answer choices -the left outer (natural) join -the right outer (natural) join -the full outer (natural) join -the inner (natural) join Assume {A, B} is...
Econ 178-001 International Trade Problem Set 1 All of the questions in this assignment refer to...
Econ 178-001 International Trade Problem Set 1 All of the questions in this assignment refer to material found in Chapter 9 on tariffs and quotas. 1. Home is a small country with a demand curve for wheat described by QD = 1000 – 100P. Its supply curve is described by QS = 100P. *please part e-h only* a. Graph this market. What is the equilibrium price and quantity in the absence of trade? b. Calculate the consumer and producer surplus...
• Relational Schema Create a relational database schema consisting of the four relation schemas representing various...
• Relational Schema Create a relational database schema consisting of the four relation schemas representing various entities recorded by a furniture company.   Write CREATE TABLE statements for the following four relation schemas. Define all necessary attributes, domains, and primary and foreign keys. Customer(CustomerID, Name, Address) FullOrder(OrderID, OrderDate, CustomerID) Request(OrderID, ProductID, Quantity) Product(ProductID, Description, Finish, Price) You should assume the following: Each CustomerID is a number with at most three digits, each OrderID is a number with at most five digits,...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT