Explore BrainMass
Share

SQL statements and databases

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

This must be done in SQL Server 2005.

In the first exercise, the Class field in the Part table should be a string of size 5, and not an int.

1- Write a statement that creates a table named Part, with an Id field as an int idendity primary key(PK), a SupplierId int field, a Description string field of size 25, a Count int field, a Class string field of size 5, and an inspection small date field. All fields cannot have a value of null, and the SupplierId field should be an FK that references the PK of a Supplier parent table.

2- Write a satement to add a check constraint to the above defined table, which guarantees that values entered in the inspection small date field are not older than today, meaning from now on. According to new requirements from your business analyst, an inspection can only be scheduled in the future.

3- Write a statement to add a check constraint to the above defined table, which guarantees that values entered in the Count field are never less than zero.

4- Write a statement to add a check constraint to the above defined table, which guarantees that values entered in the Class field can only be limited to the following strings: Open, Spec, and Priv.

5- Write a statement to create a view that selects all fields from the above table, except for both Id fields, in addition to the supplier name field of the Supplier table referenced in question 1. Note that the user is only interested in Parts that are not of class Priv. In other words, parts of class Priv should not be returned. Hint: A JOIN must be used to only return common rows.

© BrainMass Inc. brainmass.com October 24, 2018, 11:25 pm ad1c9bdddf
https://brainmass.com/computer-science/data-clustering/193580

Solution Summary

3-part solution is attached in Word files and gives statements with check constraints and certain field hides.

$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