SQL

Build a small, simple database for your personal use. Some suggestions might be an address book, tax deduction tracking, or automotive history. You must use a relational database management system (RDBMS). You may use any RDBMS you wish (e.g. Access, SQL Server, Oracle, DB2, MySQL, etc.), although if you’re unfamiliar with any product, then you will probably find Access to be the easiest to use. Your database must have at least 2 related tables. All of your tables should be fully normalized, such that redundant data is removed. You must show how your tables were created (i.e. Design Views or SQL). You must include all applicable integrity constraints (i.e. primary keys, foreign keys, required/NOT NULL, unique, and validation rules/CHECK constraints). Submit for grading a single Word or .pdf file with screen snapshots showing all steps in the creation of your database, the creation of your tables, the population of your tables with at least 3 rows of data each, and at least 3 queries of your data. Do not submit your actual database. Include at the end of your Word or .pdf file a 200- to 350-word write-up to respond to the following: • As you built your database, what challenges did you encounter? What tips do you have for your fellow students? Learn More

You designed an OLAP database for Home Warrany Corporation. Based on that design, submit the following two SQL statements.

1. Use the CUBE extension to show the sum of costs aggregated by all permutations of appliance serial,  employee id, customer id, and year.
2. Use the GROUPING SETS extension to show the same data.

Learn More

Murach’s SQL for SQL Server Chapter 10 How to create and maintain databases and tables with SQL statements
Exercises
1. Create a new database named Membership.
2. Write the CREATE TABLE statements needed to implement the following design in the Membership database. Include reference constraints. Define IndividualID and GroupID with the IDENTITY keyword. Decide which columns should allow null values, if any, and explain your decisions. Define the Dues column with a default of zero and a check constraint to allow only positive values.
3. Write the CREATE INDEX statements to create a clustered index on the GroupID column and a nonclustered index on the IndividualID column of the GroupMembership table.
4. Write an ALTER TABLE statement that adds a new column, DuesPaid, to the Individuals table. Use the bit data type, disallow null values, and assign a default Boolean value of False.
5. Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP database. The first should allow (1) PaymentDate to be null only if PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater than zero. The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal.
6. Delete the GroupMembership table from the Membership database. Then write a CREATE TABLE statement that recreates the table, this time with a unique constraint that prevents an individual from being a member in the same group twice.

Learn More

POS/410 SQL for Business Week 4

SQL Reports

Use tables from the Kudler_FF database to write the SQL statements necessary to perform the following data manipulation:
o Increase all employees salaries with the selected EEO-1 classification by 10%.
o Increase all employees salaries by 5%.
o Choose an employee from the Employee table and delete that employee.
Deliverable: Copy the SQL statements and data from both tables showing how data was affected to a Word document. Post the document to the assignment tab of the classroom.

Use tables from the Kudler_FF database to write SQL statements that perform the following computations:
o Calculate the average salary for all employees.
o Calculate the maximum salary for all employees.
o Calculate the maximum salary for exempt employees.
o Calculate the minimum salary for all employees.
o Calculate the minimum salary for non-exempt employees.
Deliverable: Copy the SQL statements and results to a Word document. Post the document to the assignment tab of the classroom.

Use tables from the Kudler_FF database to write the following queries, using the GROUP BY clause in your SQL statements:
o Count the number of employees, grouped by EEO classification.
o Count the number of employees, grouped by exemption status.
o Compute the average salary, grouped by area code and gender.
o Compute average age, grouped by exemption status o Compute the earliest hire date, grouped area code, gender, and exemption status. Deliverable: Copy the SQL statements and results to a Word document. Post the document to the assignment tab of the classroom.

Learn More

Install the Northwind Database into the environment. Click here to install the database.
Note that this link does link to the SQL Server 2000 Sample Databases, but after downloading it, the ZIP file can be extracted and contained inside the package as a file named "instnwnd.sql." This file can be opened and executed inside of Microsoft SQL Server Management Studio.

Task #2
Generate a database diagram.
Include a screenshot of this diagram in your Word document.

Task #3
Write SELECT statements for the following questions:
For each of these statements, include a screenshot of the SQL. Make sure to include the statement execution, including the resulting data.
1. Display all columns and all rows from the Employees table.
9 rows returned
2. Display the regionid, regiondescription for all rows in the Regions table.
4 rows returned
3. Modify query 2 so that the column headings are as follows:
"ID" and "Description"
4 rows returned
4. Display the customer ID, customer name, and region for all customers.
91 rows returned
5. Display the customer ID, customer name, and region for all customers.
Name the columns "ID," "Name," and "Region" (respectively), and sort the data by the company name.
91 rows returned

Learn More

MIS562 SQL Database assignment

1. Show a list of all employee names and their department names and the employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join.

2. Select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join.

3. Write a query that that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax.

4. Write a query that that performs an inner join of inner join of the grade, student, and grade_type tables using the Oracle inner join convention.

5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a set operator.

6. Write a SQL statement using a set operator to show which students enrolled in a section that are not enrolled in any classes. Exclude students with student id less than 300.

Learn More

South Community College (SCC) is structured like a typical community college. They have 3 semesters and a multitude of courses. Each course may have any number of sections in a given semester. For example, CTS2437 (SQL Server) may have one or more sections being taught in any given semester. SCC has 3 semesters (fall, spring, summer) which they refer to as A, B, and C. They refer to semesters by the year and the semester code. For example, fall 2011 would be referred to as 2011A. They need to keep track of students, courses, schedules, instructors, and grades earned in each course taken. They need a database to maintain these information. The student information would include the student name, address, phone#, and email. Students may have taken or are taking any number of courses. The grade earned for each course must also be maintained. The course information would include the course title and number of credits. Keep in mind that a given course may have many sections in any particular semester. SCC needs to maintain the instructor for each section in addition to the students and the grade they earned. The instructor information would include the instructor name, phone#, office#, and email address. South needs to maintain all courses that the instructor has taught or is currently teaching. Some of the requirements that SCC has requested in the database system include: • Student cannot register for the same section more than once. • A roaster of students can be produced for any given section. • GPA (Grade Point Average) can be generated for a student for any given semester, year, or entire school career. • GPA is calculated by adding up all of the grades earned (A=4, B=3, C=2, D=1, F=0) and dividing by the number of credits associated with the courses taken. • A transcript can be produced for a given student showing all courses taken and grades earned. Learn More