SQL

CS362 Structured Query Language for Data Management Week 3 IP Select Statements:

Provide select statements to satisfy the following data requests:
1. List all active male students assigned to Advisors 1 or 3 (Fred Stone or Jack Simpson). (Where Clause - Filtering 3 different things: Active, Male Students, Adivsors)
2. Provide a list of all students without a biography. (focusing Null Biography)
3. What classes are in the English department? (Where Clause)
4. Create a list of all students and their advisors. Sort by the advisor’s name and then the student’s name. Include the student’s birth date, gender, and GPA. (join's statement combine the two tables students and class tables: Order by clause like birthday, gpa,etc)
5. How many students were born in the 1980s? (Aggregate using count expression)
6. Write a query to show the average GPA by gender. (Aggregate using count expression)
7. Provide a list of all advisors and the number of active students assigned to each. Filter out any advisors with more than 1 student. (Combine lines 4-6)

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Include an explanation as to how and where these queries or others like them can be used in your final system. Upload your document to the Submitted Tasks.

Learn More

CS362 Structured Query Language for Data Management Week 2 IP

After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information.

1. Insert the following classes' records:
Code     Name     Description
ACCT306     Accounting 1     This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.
CS362     Structured Query Language for Data Management     This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.
ENG115     English Composition     In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.
FIN322     Investments     This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.

2. Insert the following advisors' records:
Name     Email
Fred Stone     fred@college.edu
Bob Gordon     bob@college.edu
Jack Simpson     jack@college.edu

3. Insert the following students' records:
Name     Birthdate     Gender     StartDate     GPA     IsActive     AdvisorID
Craig Franklin     1970-03-15     Male     2010-05-30     3.10     Yes     3
Harriet Smith     1982-04-15     Female     2010-05-30     3.22     Yes     1
George David     1984-11-05     Male     2010-10-01     0.00     Yes     3
Ben Jefferson     1976-09-25     Male     2009-02-21     1.80     No, the student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year.     3

4. Delete the course named Investments from the system.

5. Change Harriet Smith’s birthdate to April 25, 1982 and her GPA to 3.25.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Provide an introduction explaining the important of these commands in relation to your overall Key Assignment.

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

CIS515 Assignment 2: Database Systems and Database Models

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server.

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finance, marketing, sales, and operations. The sales department is the only department where employees are paid a commission in addition to their yearly salary and benefits. All other departments compensate their employees with a yearly salary and benefits only. Commission is paid by multiplying the employee’s commission rate by the total amount of product units sold. You have access to the following data sets:

Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID)
Invoice (InvNumber, InvDate, EmpNumber, InvAmount)
InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity)
Product (ProductNumber, ProductDescription, ProductCost)
Department (DepartmentID, DepartmentDescription)
Job (JobID, JobDescription)

Write a two to three (2-3) page paper in which you:

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December. Note: You will need to generate the tables described above (Employee, Invoice, InvoiceLine, Product, Department, and Job) in order to compare and validate your code. Validated query code must be part of your paper.
Compare the code of the query you designed in Question one (1) to one that would show how much total compensation is paid to each employee for the same month.
Determine and explain the factors necessary to ensure referential integrity.
Create an object-oriented model to show how the tables are interrelated through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. Additionally, remember to include the determined factors from the previous assignment requirement. Note: The graphically depicted solution is not included in the required page length.
Identify which data components are the entities and attributes, and the relationship between each using an object representation diagram through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

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

CTS2437 – Final Exam Provide the SQL statements required to accomplish the following tasks. #1 (10 points) Create a database named FINAL_EXAM that you will then use for all remaining problems. #2 (20 points) Create the tables and appropriate constraints based on the following ER diagram. Use appropriate data types. Note that the size column should only accept S, M, or L. In addition the price column should have values greater than zero. All columns in both tables are required. Catgeory Product C #3 (5 points) Insert 3 rows in the Category table. The db is for a small shoe store, so use appropriate data for the description ( “Men”, “Women”, “Children”) #4 (5 points) Insert 3 Product records for each category in the product table. Use whatever data you see as appropriate. #5 (5 points) Use one statement to increase the price of all products in the Men category by 25%. #6 (5 points) Use one statement to delete all products for the Children category. #7 (10 points) Create and execute a view named EXAM_VIEW that shows all columns from both tables. Use an inner join. #8 (10 points) Create a database trigger named EXAM_TRIGGER that prevents a user from deleting a Product record on Tuesdays. Display an appropriate error message. Make sure to show that the trigger is working properly. #9 (10 points) Create a stored procedure named SP_EXAM that will be used to insert records into the Product table. Make sure to show that the procedure is working properly. #10 (5 points) Remove the EXAM_VIEW object from the database. #11 (5 points) Remove the SP_EXAM stored procedure from the database. #12 (5 points) Remove the EXAM_TRIGER database trigger from the database. #13 (5 points) Remove the FINAL_EXAM database. Learn More