SQL

Need Help in SQL Assignment?
We can help you if you are having difficulty with your SQL Assignment. Just email your SQL Assignment at admin@assignmentcache.com.
We provide help for students all over the world in SQL Assignment.

Items 1 to 10 of 20 total

per page
Page:
  1. 1
  2. 2

Grid  List 

Set Descending Direction
  1. DBM449 LAB1 SqlFile

    DBM 449 LAB 1 Oracle Joins

    Regular Price: $20.00

    Special Price: $5.00

    GENERAL OVERVIEW
    Scenario/Summary
    My colleague, Ann Henry, operates a regional training center for a commercial software organization. She created a database to track client progress so she can analyze effectiveness of the certification program. CLIENT, COURSE, and COURSE_ACTIVITY are three of the tables in her database. The CLIENT table contains client name, company, client number, pre-test score, certification program and email address. The COURSE_ACTIVITY table contains client number, course code, grade, and instructor notes. The COURSE table contains the course code, course name, instructor, course date, and location. Although she and her instructors enter much of the data themselves, some of the data are extracted from the corporate database and loaded into her tables.

    Loading the initial data was easy. For grade entry at the end of each course, a former employee created a data entry form for the instructors. Updating most client information and generating statistics on client progress is not easy because Ann does not know much SQL. For now, she exports the three tables into three spreadsheets. To look up a grade in the COURSE_ACTIVITY spreadsheet, she first has to look up client number in the CLIENT spreadsheet. While this is doable, it is certainly not practical. For statistics, she sorts the data in the COURSE_ACTIVITY spreadsheet using multiple methods to get the numbers she needs.

    Every month, Ann's database tables need to be refreshed to reflect changes in the corporate database. Ann describes this unpleasant task. She manually compares the contents of newly extracted data from corporate to the data in her spreadsheets, copies in the new values, and then replaces the database contents with the new values.

    Ann needs our help. Let’s analyze her situation and determine what advanced SQL she could use to make her tasks easier.
         
    L A B O V E R V I E W


    Scenario/Summary


    The purpose of this lab is to explore join operators to determine which, if any, are appropriate for solving Ann's business problems, as described in this week's lecture.


    Since Ann prefers to work from Excel spreadsheets, she wants her CLIENT and COURSE_ACTIVITY tables exported into one spreadsheet rather than two, as she is currently using. We need to determine which, if any, of the join operators will provide the data she wants for the single spreadsheet. (Note: we will not perform the export, just determine how to retrieve the necessary data.) Using the spreadsheet, she will be able to determine:



    1. Which course(s) a specific client has taken

    2. What grade(s) a specific client has earned in a specific course

    3. Which clients did not take any courses

    4. Which courses were not taken by any client


    Here are results from DESCRIBE commands that show structure (columns and their data types) of tables CLIENT and COURSE_ACTIVITY. You may refer to it while constructing your queries.


    SQL*Plus: Release 10.2.0.1.0 — Production on Thu Jun 14 22:38:52 2007

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 — 64bit Production
    With the Partitioning, OLAP and Data Mining Options

    SQL> desc course_activity



    SQL>


    For this lab you will be creating several documents. First, write your queries in Notepad to create a script file that will contain all of the queries asked for in lab steps 4 through 13. You can (and should) test each query as you write it to make sure that it works and is returning the correct data. Once you have all of your queries written then create a SPOOL session and run your entire script file. Be sure that you execute a SET ECHO ON session command before running the file so that both the query and the output will be captured in the SPOOL file. IMPORTANT: If you are using Windows Vista you will need to create a directory on your C: drive to SPOOL your file into. Vista will not allow you to write a file directly to the C: drive. This will give you two files for the lab. The third file will the be the Lab1 Report document found in Doc Sharing. You will need to put your responses to the questions asked in the various lab steps.


    Now let's begin.


    L A B S T E P S

    STEP 1: Start Oracle SQL*Plus via Citrix


    Log into the Citrix iLab environment. Open your Oracle folder, select SQL Plus and log in to your database instance. Use "sys" as User Name, and "oracle" as the Password. Enter the Host String as "DB9999.world as sysdba" where 9999 is the database number you have been assigned.


    STEP 2: Initialize tables


    Download the pupbld.sql and Lab1_init.sql files associated with the links to your C: drive or to the F: drive in your Citrix environment. You will need to open each of the files and edit the connection string to reflect your instance name. The pupbld.sql file has two connections strings; one at the top of the script and another at the bottom. Be sure to change both of these to reflect your instance name.


    Once you have done this then run the pupbld.sql script first (DO NOT copy and paste it) in your SQL*Plus session. The script will create the product_user_profile synonym in the SYSTEM account which will be used each time you log in as a normal user.


    Next run the lab1_init.sql script in your session. The script will create a new user (DBM449_USER) that will be used in various labs in this course. You can find the password for this new user by looking at the CREATE USER statement in the script file. Disregard the DROP TABLE error messages. They occur because the script is designed to work regardless of whether you have already created the tables or not. This way, you may run it if you ever decide to resent the contents of your tables to the original values. When you run the script for the first time, the error messages appear as you attempt to drop tables that do not exist.


    Once the script has finished you will be logged into the new user and ready to start your lab.


    STEP 3: Verify your tables


    You want to verify that everything completed successfully. To do this execute a SELECT * FROM TAB statement to make sure all 5 tables were created and then you can execute a SELECT COUNT(*) FROM statement using each of the table names. You should find the following numbers of records for each table.



    • CLIENT table - 5 rows

    • COURSE table - 5 rows

    • COURSE_ACTIVITY table - 6 rows

    • CORP_EXTRACT1 table - 3 rows

    • CORP_EXTRACT2 table - 0 rows


    NOTE: In the following steps when writing your queries be sure to list the tables in the FROM clause in the same order they are listed in the instructions. Reversing the order of the tables in the FROM clause will produce an incorrect results set


    STEP 4: Using the FULL OUTER JOIN operator


    Join the CLIENT and COURSE_ACTIVITY tables using a FULL OUTER JOIN.



    • Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.


    Will the FULL OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.


    STEP 5: Using the RIGHT OUTER JOIN operator


    Join the CLIENT and COURSE_ACTIVITY tables using a RIGHT OUTER JOIN.



    • Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.


    Will the RIGHT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.


    STEP 6: Using the LEFT OUTER JOIN operator


    Join the CLIENT and COURSE_ACTIVITY tables using a LEFT OUTER JOIN.



    • Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.


    Will the LEFT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.


    STEP 7: Using the NATURAL JOIN operator


    Join the CLIENT and COURSE_ACTIVITY tables using a NATURAL JOIN.



    • Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

    • Will the NATURAL JOIN be helpful to Ann? Place your response in the lab report document for this step.


    STEP 8: Using the INNER JOIN operator


    Join the CLIENT and COURSE_ACTIVITY tables using a INNER JOIN.
    Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.


    Will the INNER JOIN be helpful to Ann? Place your response in the lab report document for this step.


    Write a conclusion based on the five steps above, which join - if any - should Ann use to populate the spreadsheet that can answer her questions.


    STEP 9: Using the UNION operator 


    Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION operator.



    • Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.


    Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.


    STEP 10: Using the UNION ALL operator


    Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION ALL operator.



    • Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.


    Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.


    STEP 11: Using the INTERSECT operator


    Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the INTERSECT operator.



    • Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.


    Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.


    STEP 12: Using the MINUS operator


    Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the MINUS operator.



    • Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.


    Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.


    STEP 13: Using subqueries


    Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using a subquery with NOT IN operator.



    • Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.

    • Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.


    Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.


    Deliverables
        
    What is Due


    Submit your spooled lab file with the queries and results sets along with the completed Lab 1 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

    Learn More
  2. CS362 Structured Query Language for Data Management Week 4 IP Extended Design SQL Server

    CS362 Structured Query Language for Data Management Week 4 IP Extend the Design

    Regular Price: $20.00

    Special Price: $15.00

    CS362 Structured Query Language for Data Management Week 4 IP Extend the Design

    Using your Week 1 IP as a starting point, extend the design to accommodate degree programs. The new design should incorporate the following functionalities:

    1. A degree has a name and description.
    2. What degree is the student working towards? A student can only work on one degree at a time.
    3. What classes are necessary to obtain a specific degree?
    4. Provide the DDL script to add the tables for the new design changes. Include an updated diagram that shows all the tables in the system.
    5. Write the DML script to insert 3 test records in each of the tables in the system. This data will be necessary to write the queries in the next assignment.

    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. Upload your document to the Submitted Tasks section.

    Learn More
  3. CS362 Structured Query Language for Data Management Week 5 IP Functions MySQL

    CS362 Structured Query Language for Data Management Week 5 IP Functions Stored Procedure and Indexes

    Regular Price: $20.00

    Special Price: $15.00

    CS362 Structured Query Language for Data Management Week 5 IP Functions Stored Procedure and Indexes

    This assignment requires you to take your extended design from Week 4 IP and add proper indexes, a function, and a stored procedure. Your changes will provide functionality used by the teachers for screens such as a grade book. Be sure any code is properly formatted and has appropriate comments.

    Part 1: Function
    Write a user-defined function (UDF) that calculates a student's GPA for a given time frame. Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. The output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice.

    Part 2: Stored Procedure
    Write the DDL script to make a stored procedure that returns data needed to display a grade book screen for a professor. The only input for the stored procedure is a ClassId. Outputs need to include student names and grades for all assignments as well as a calculated overall grade for the class for each student. Provide an example calling this new stored procedure, passing it parameter values of your choice. Include a screenshot of the output.

    Part 3: Indexes
    Provide a list of suggested indexes and the DDL script to create them. Include an explanation of the purpose of indexes and how you made your decision for the fields to include in your list of suggested indexes.


    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. Upload your document to the Submitted Tasks.

    Learn More
  4. Unit 4 Individual Project ddl

    ITCO231 Unit 4 Individual Project Database SQL Server

    Regular Price: $20.00

    Special Price: $15.00

    Unit 4 Individual Project: Database SQL Server

    Deliverable Length:
    1 Database Diagram; DDL for 7 Tables

    Details:
    The following is the same database diagram from Unit 3:

    Using this diagram, address the following:
    Establish the relationships between the tables in the SQL server environment.
    Ensure that all primary keys are properly created and that the foreign key columns are defined correctly.
    Make sure that the 3 additional tables you added in Unit 3 are also shown and have established primary and foreign keys to appropriate tables.
    Update the database diagram, and generate the data definition language (DDL) for the new tables.
     Describe what changed in the DDL for each table.
     Describe any changes that were needed from the original model to create the relationships.
    Submit a consolidated Word document with all screenshots and the DDL.

    Learn More
  5. Unit 5 Individual Project Database SQL

    ITCO231 Unit 5 Individual Project Database SQL Server

    Regular Price: $20.00

    Special Price: $15.00

    Unit 5 Individual Project Database SQL Server

    Deliverable Length: 7 SELECT statements; 3 SQL JOIN statements

    At this point, you will add data to your database and validate that they loaded properly. In tabular format, include 3 rows for each table, making sure that the primary-key and foreign-key relationships are properly applied.

    Next, you will insert the 30 rows of data that you identified (using the concepts you worked on identifying the primary and foreign keys), then perform queries using JOIN syntax of the database.

    Task 1: Create 3 rows of data for each table ensuring that the referential integrity is valid.
    Task 2: Add the 30 rows of data to the appropriate table in your database (using any appropriate method available).
    Task 3: SELECT all columns and all rows of the 10 tables. Create a screenshot of each query and output data, and submit them.
    Task 4: Write SELECT statements for the following (include a screenshot of the SQL and its execution, including the resulting data):
    Display the employee id, first_name, last_name, and department_name for all employees.
    Rows returned
    Display the employee id, first_name, last_name, and job title_name for all employees.
    Rows returned
    Display the employee id, first_name, last_name, department_name, and job title_name for all employees.
    Rows returned

    Combine all of the SQL statements (text only) and screenshots into a single Word document, and submit it for grading

    Learn More
  6. ITCO630 Unit 3 Individual Project SQL Script

    ITCO630 Unit 3 Individual Project SQL Script

    Regular Price: $20.00

    Special Price: $15.00

    ITCO630 Unit 3 Individual Project SQL Script

    Deliverable Length: SQL Script

    Unzip, and attach the sample database ITCO630_A. The sample database represents an educational institution with students and different schools. Each student goes to just one school. The students have various roles in different assignments and may work on more than one assignment at a time.
    The following are the tables and data in the ITCO630_A database:

    Using the sample database, write the scripts in a file called ITCO630_P3.SQL to create the following views. Remember to include a USES clause at the top of the script file to use the ITCO630_A database. Also include code that checks if the view already exists. If it does, it should be dropped and recreated.

    1. Create a view named v_worker showing the student number, assignment number, and start date where the role is "worker."
    2. Create a view called v_no_points with all the columns of the assignment table except the points column.
    3. Create a view called v_count that shows the number of students working on each assignment. The view should have columns for the assignment number and the count.

    Learn More
  7. INF322 Week 5 Final Project Customer Service Access Database

    INF322 DATABASE MANAGEMENT Week 5 Final Project Customer Service Database

    Regular Price: $20.00

    Special Price: $15.00

    INF322 DATABASE MANAGEMENT Week 5 Final Project Customer Service Database

    The Final Project consists of building a customer service database to be submitted by the final day of the course.

    Focus of the Project
    1. Use the Access database you created in Week 3: Customer Service Database

    2. Output 3 queries using SQL code and not the standard access view:
    A query on all contacts in the customers table showing first name, last name, address, e-mail address and three additional fields from the table.
    An update query that changes one or more customer records but not all records.
    A delete query that removes one or more customer records but not all records.

    3. Using SQL commands, add a new field called Birthday to the Customers table. Populate some of the fields with data.

    4. Report on Customers in Database showing Names, State, and Phone Numbers.

    5. Create a report showing contact name and birthdays. Do not show records without a birthday.

    6. Analyze the data populated in the Database to show referential integrity. This includes a statement showing what the affected tables in each dataset are and how they are impacted in the case of an addition of data or a deletion of data.

    7. All data and screen shots should be included in a Word document with a brief explanation of the actions you performed.

    Learn More
  8. COM 330 Chapter 11 Assignment

    COM 330 Chapter 11 Assignment

    Regular Price: $20.00

    Special Price: $15.00

    COM 330 Chapter 11 Assignment

    Find the solutions to Problems 1 and 2 based on the following query:
    SELECT    EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX
    FROM    EMPLOYEE
    WHERE    EMP_SEX = ‘F’ AND EMP_AREACODE = ‘615’
    ORDER BY    EMP_LNAME, EMP_FNAME;

    1. What is the likely data sparsity of the emp_sex column?
    2. What indexes should you create? Write the required sql commands.


    Problems 4-6 are based on the following query:
    SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR
    FROM EMPLOYEE
    WHERE YEAR(EMP_DOB) = 1966;

    4. What is likely data spatsity of the EMP_DOB column?
    5. Should you create an index on EMP_DOB?
    6. What type of database I/O operations will likely be used by the query?

    Learn More
  9. Home Warrany Corporation HWC CUBE and GROUPING SETS SQL Statements

    Home Warrany Corporation HWC CUBE and GROUPING SETS SQL Statements

    Regular Price: $15.00

    Special Price: $12.00

    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
  10. CS362 Structured Query Language for Data Management Week 3 IP Select statements

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

    Regular Price: $20.00

    Special Price: $15.00

    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

Items 1 to 10 of 20 total

per page
Page:
  1. 1
  2. 2

Grid  List 

Set Descending Direction