User Account

All Pages

Write sql code that will create only the table structure

Assignment Help PL-SQL Programming
Reference no: EM133868940

Introduction to the Relational Database

SQL & NoSQL- Group Assignment

Purpose
The purpose of the assignment is to provide students with the opportunity to apply knowledge and skills developed during the semester regarding:

Apply skills learned in the SQL lecture to build a physical database
Application of DDL and DML components of SQL to:

creates and populates a relational database; and

Query to retrieve the data from the table

SQL to answer the provided questions

Section A
Create a database and name it

-Use the created database to create all the above tables. Make sure to include all the constraints, such as primary key, foreign key, Null, Not Null, and appropriate data type, and provide a screenshot after each table is created. Get in touch with us for online assignment help service!

Insert the data as provided in the above tables and provide a screenshot after inserting the data for each table.

Section B

Write SQL code that will create only the table structure for a table named EMPLOYEE1. This table will be a subset of the EMPLOYEE table. The basic EMPLOYEE1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME.

Write the SQL code to enter the first three records (the first three records should be the group members' first and last names). Use the hire date and job_code of the three records in the Employee1 Table below. Each row should be inserted individually without using a subquery.

Using the EMPLOYEE table that already exist, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMPLOYEE1 table. Remember, your sub-query should only retrieve the column needed for the EMPLOYEE1 table and only the employees shown in the figure.
write the SQL code that will save the changes made to the EMPLOYEE2 table.
write the SQL code to change the job code to 507 for the person whose employee number (Emp_Num) is 107.
Write the SQL code to delete the row for John Alex, who was hired on November 08, 2000, and whose job code is 504. (Hint: Use logical operators to include all the information given in this problem. Remember, if you are using MySQL, you will have to first disable "safe mode.")

Write the SQL code to create a copy of EMPLOYEE1, including all its data, and name the new table EMPLOYEE2.
Using the EMPLOYEE2 table, write the SQL code that will add the attribute PCT and ProjNum to EMPLOYEE2. The PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are:
PCT Numeric (5,2)
ProjNum Int

Using the EMPLOYEE2 table, write the SQL code to change the PCT value to 3.5 for the person whose employee number is 105.
Using the EMPLOYEE2 table, write the SQL code to change the PCT value to 7.5 for the employees whose employee numbers are 101,103, and 104.

Using the EMPLOYEE2 table, write a single command to change the PCT value to 15 for all employees who do not currently have a value for PCT.
Using the EMPLOYEE2 table, write a single command to add 0.75 to the PCT value of the employee's name is Fenny Fenny. (Use logical operator and Lame and FName).
Write SQL code to display the employee number, first name and last name, and project number and project name for the employees who not working on the project.
(Use EMPLOYEE and Project tables).
Write SQL code to display the employee number, first name and last name, and project number and project name for the employees who not working on any project. (Use sub-query) (Use EMPLOYEE and Project tables).
Write SQL code to find the total balance and name the field as Total Balance.
Write SQL code to display the employee's First name, and last name who have the same job code, and order the result by first name in ascending order.
Write SQL code to create virtual table For Employee1 table and name EmpVirtual and it includes the following fields (Emp_Num, LName, FName, and Job_Code) and for only employees not assigned to any project).

Write SQL code to display the results of EmpVirtual.

Section C
use a reverse engineering and draw and ERD based on the provided tables (Employee, Job, Assignment, and Project).

Use MongoDB (Studio3T) to create a database named Assi2GroupNo based on your Group Number.
Write a code to create a collection named an Assignment.
Write a code to insert the rows in the assignment table.
Write a code to display the database, Collection, and all inserted records.
Write a code to display all the records for Emp_Name, Julia.
Write a code to display all the records for Emp_Num that are not equal to 110.

Reference no: EM133868940

Questions Cloud

Lisinopril for hypertension and order baseline serum : You prescribe a 65 year old patient 5 mg of lisinopril for hypertension and order a baseline serum creatinine,
Complaining of feeling low-having difficulty sleeping : A 36-year-old man is seen in the outpatient department complaining of feeling low, having difficulty sleeping,
Difference between compliance-adherence and concordance : Discuss the difference between compliance, adherence, and concordance, and provide a patient-related example of each,
Discuss your approach when you face these situations : Discuss your approach when you face these situations. As you enter the classroom on day of the lab, you suddenly realize that you are wearing your flip-flops.
Write sql code that will create only the table structure : Write SQL code that will create only the table structure for a table named EMPLOYEE1. This table will be a subset of the EMPLOYEE table. The basic EMPLOYEE
Building and analysing advanced machine learning models : Building and Analysing Advanced Machine Learning Models Assessment - implement and evaluate Neural Network-based models (e.g., image and text classification)
What is the companys primary strategy : What is the company's primary strategy, and is it working well? What are the company's strategic resources and capabilities (consider VRIO)?
Differentiate between HITECH and HIPAA laws for therapists : Differentiate between HITECH and HIPAA laws for therapists. What are some key concerns in providing therapy through electronic or virtual delivery
Beta blocker for congestive heart failure : You desire to prescribe a 67 year old patient a beta blocker for congestive heart failure. The patient has comorbid hypertension, obesity,

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Create a database model

Create a database model and Submit the table creation statements for the Database Model.

  Write pl-sql procedures and functions

Write PL/SQL procedures and functions to populate and query that database

  Sql questions

Write a query to display using the employees table the EMPLOYEE_ID, FIRST_NAME, LAST_NAME and HIRE_DATE of every employee who was hired after to 1 January, 1995.

  Run the lab_03_01.sql script

Run the lab_03_01.sql script in the attached file to create the SAL_HISTORY table. Display the structure of the SAL_HISTORY table.

  Write sql queries

Write a query to display the last name, department number, and salary of any employee whose department number and salary both match the department number and salary of any employee who earns a commission.

  Explaining sql insert statement to insert new row in cds

Write down a SQL insert statement to insert new row in "CDS" table.

  Write down name of actors in ascending order

Write down actors (or actress, your choice, but not both) who have won at least two (2) Academy Awards for best actor/actress. Provide the actor name, movie title & year. Order the result by actor name."

  What is an sql injection attack

What is an SQL injection attack? Explain how it works, and what precautions must be taken to prevent SQL injection attacks.What are two advantages of encrypting data stored in the database?

  Determine resonant frequency in series rlc resonant circuit

Given the series RLC resonant circuit in the figure, operating at variable frequency, determine: The resonant frequency ω o ,  The circuit’s quality factor Q , The cut-off frequencies, f 1  & f 2  and the bandwidth BW

  Query that uses cube operator to return lineitemsum

Write summary query which uses CUBE operator to return LineItemSum (which is the sum of InvoiceLineItemAmount) group by Account(an alias for AccountDesciption).

  Query to show customers were missing for existing orders

As DBA, your manager called a meeting and asked why there are so many orders for customers that don't exist in the customer table. Write query which would shows which customers were missing for existing orders. Use a join or a subquery.

  Sql query into a relational algebra statement

Turn this SQL query into a relational algebra statement? SELECT Request.reqfor, Ordering.invamt, Ordering.invnbr, Ordering.invdat

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd