Explore BrainMass
Share

SQL Sample Database Queries

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

I am seeking help with solving several SQL statements. I am specifically looking for the code that goes with these problems.

6) A wide world importers company tracks its order information in a database that includes two tables: Order and LineItem. See table structures below:

CREATE TABLE dbo.Order (
OrderID int NOT NULL,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
CONSTRAINT DF_Order_OrderDate DEFAULT (getdate())FOR OrderDate,
CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderID)
)
CREATE TABLE dbo.LineITEM (
ItemID int NOT NULL,
OrderID INT NOT NULL,
ProductID int NOT NULL,
Price money NOT NULL,
CONSTRAINT PK_LineITEM PRIMARY KEY CLUSTERED (ItemID),
CONSTRAINT FK_LineITEM_Order FOREIGN KEY (OrderID)
REFERENCES dbo.Order (OrderID)
)

The company's auditors have discovered that every item that was ordered on June 1, 2000, was entered with a price that was $10 more than its actual price. You need to correct the data in the database as quickly as possible.

7) A sporting goods company exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database.

CREATE TABLE customers (
CustmerID int NOT NULL,
CustomerName varchar(30) NOT NULL,
ContactName varchar(30) NULL,
Phone varchar(20) NULL,
Country varchar(30) NOT NULL,
CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
)

There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company's marketing department wants to target its advertising to countries that have more than 10 customers. You need to create a list of these countries for the marketing department.

****Feel free to input your own countries for this*****

© BrainMass Inc. brainmass.com October 24, 2018, 6:27 pm ad1c9bdddf
https://brainmass.com/computer-science/data-clustering/sql-sample-database-queries-40981

Attachments

Solution Preview

Attached.

6) A wide world importers company tracks its order information in a database that includes two tables: Order and LineItem. See table structures below:

CREATE TABLE dbo.Order (
OrderID int NOT NULL,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
CONSTRAINT DF_Order_OrderDate DEFAULT (getdate())FOR OrderDate,
CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderID)
)
CREATE TABLE dbo.LineITEM (
ItemID int NOT NULL,
OrderID INT NOT ...

Solution Summary

A sample of database queries are provided. Order and Line items are determined.

$2.19
See Also This Related BrainMass Solution

Writing SQL Queries Using a Database and Tables

Write SQL queries (SELECT statements) using BETWEEN, LIKE, UNION, and GROUP.

Using the database and tables (see attachment) write SQL INSERT statements to insert records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location. Check your results by selecting all of the columns from both of your tables.

Using the database and tables (see attachment), write SQL queries using BETWEEN, LIKE, and UNION:

- Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use Emp_salary to restrict the data.)
- Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use Hire_date to restrict the data.)
- Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use Telephone_area_code to restrict data.)
- Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use Emp_age to restrict data.)
- Write a SQL query that uses UNION of the two tables to produce a combined result set.

Using the updated database that includes employees from administrative offices and the Delmar location, write the following queries using the SQL GROUP BY statement:

- Group employees by job classification: Select the employees' last names and group them by EEO_1_classification.
- Group employees by salary: Select the employees' last names and group them by Emp_salary.
- Group employees by salary within their job classification: Select the employees' last names and group them by Emp_salary within their EEO_1_Classification.
- Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt.

In Microsoft SQL Server Management Studio, save your SQL statements as .SQL files. Save your SQL output as .RPT files. You will need to post both types of files (.SQL and .RPT).

View Full Posting Details