Complete all of the questions in this SQL file and submit the file for grading Open this file in SQL Workbench to complete all of the statements Make sure you run the CreateDB Script to create the sample database again so you have the correct data You will need it to create the queries based on these tables There is a .jpg file which shows the tables in the database */ /* Question 1 a) Create two tables with the same numbers of columns and datatypes (mininum 3 columns in each table) (3 marks) b) Populate that table with data (3 marks) c) Create a SELECT statement for each table and UNION them together (4 marks) */
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Complete all of the questions in this SQL file and submit the file for grading
Open this file in SQL Workbench to complete all of the statements
Make sure you run the CreateDB Script to create the sample
You will need it to create the queries based on these tables
There is a .jpg file which shows the tables in the database
*/
/*
Question 1
a) Create two tables with the same numbers of columns and datatypes (mininum 3 columns in each table) (3 marks)
b) Populate that table with data (3 marks)
c) Create a SELECT statement for each table and UNION them together (4 marks)
*/
/*
Question 2
Create a query that lists the department number, employee number, and salaries of all employees in department D11.
UNION the same information , but this time sum up all the salaries to create a one line summary entry for the D11 department (hint sum the salary). Sort the list by Salary.
*/
/*
Question 3
a ) Write a query that uses NATURAL JOIN TO connect the EMPLOYEE and EMPPROJACT table. Include the Employee number , First and Last name, Salary, Salary increased by 3% and Project number
b) Use INNER JOIN OR JOIN with the same query with USING statement
c) Use INNER JOIN OR JOIN with the same query with joined columns (hint a = a )
*/
/*
Question 4
a) Create three tables. Two of the tables will have PRIMARY KEYS (mininum 3 columns in each table) and the third table will have two columns that are the foreign keys to each of the PRIMARY KEYS
b) Populate these table with data
c) Create a SELECT statement using NATURAL JOINS to connect the three tables together
d) Run the Reverse Engineer function in MySQL workbench on these tables and provide the .MWB file in your submission ( 7 marks )
*/
/*
Question 5
Write a query that uses INNER JOIN TO connect the EMPLOYEE, EMPPROJACT, PROJACT and PROJECT tables. Include the Project number , Department number, Project start and end date and AC STAFF
WHERE They belong to department A00 , Salary is more than or equal to 65 percent of $15,000 AND Salary is less than or equal to 130 percent of $40,000
*/
/*
Question 6
Create a query that lists empno, projno, emptime, emendate. Left join the project to the empprojact table using projno and left join the act table using actno and then right join employee table using empno.
Where projno is AD3113 and empno is 000270 and emptime is greater than 0.5
*/
/*
Question 7
Describe all of the relationships between the tables in the attached image file TableRelationships.jpg
a) describe all the foreign key and primary keys, either by detailing them 1 by 1 or define the CREATE table statements for all the tables
b) describe the relationship between each table ( 1..1 (exactly one match) 1..n (one or more matches))
*/
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 3 images
