Using Figure P7.1 in the text as a guide, write the SQL/DDL to create the Job and EMPLOYEE tables.Note the cardinality requirements for the relationship indicates a foreign key and values are required.Write the SQL commands to add the first three records to the JOB table and the first three records tothe EMPLOYEE table.a. What happens if you create the EMPLOYEE table before the JOB table? Why?b. What happens if you add an employee record with a JOB _CODE not present in the JOB table?Why? Relational diagram106Pb Cade5005010025005045055065085001510Job Descriptionob Che Houob Last UpdateTable name: JOBJoe CODE JOB DESCRIPTION JOB CHO HOUR JOB LAST UPDATE20.Nov-1720-Nov-1724--1815102225ProgrammerSvitent AnalystEMPLOYEEEma NumEmp NameEmp ameEma InitialEmp, MireDateFES CodeEmp YearsDate DesignerDectrical EngineerMechanical EngineerCivil EngineerClerical SupportDSS AnwystApplications DesignerBo TechnicianGeneral Support-H---EvergreenAmber WaveRolling TideStarfight35.7596.75Table name: PROJECTPROJ NUM PROJ NAME PROJ VALLE1453600.00350050000805000.002650500001250084.5067 9055.78268745.9645.1034.5510.30ASSIGNMENTPAsien mAssign DateFKZPNemFC EmpNamEMPAugAi Che --H-PLNAssign MeursAssign, Charge20-Nov-1720-May-1720-Nov-1720-Nov-1720-Nov-1724-Mar-1820-Nov-1720-Nov-17FROU BALANCE EMP NUM1002350 00 1032110346.00 100500345 20 1022309000.00 107PROIECTPrej NamePre ValuePrej BalanceFR3 Song NumTable name: EMPLOYEE| EMP_NUM | EMP_UNAME | EMP_FNAME101 NewsJohnSenior DavidArbough June102Ramoras AnneJohnson AlceWillianSmithfieldAbonzoMarinMariaWashingtonCaSmith103104104105106107100109an110***111m112F113114114115116117118ClerkovisbathSmithsonTerroodsJonesBawangPriGeoffDarlenDarleneDebertAnneliseTravisGeraldWillianson AngieFrommer Janes23-Mar-18 2524-3-10 2524-15 1524-Mar-18 2224-at-10 2224-10 2224--16 1524-3-10 18117163100104113103115117105100115104163105112117104201RalphLarryPGeraldTom105115105183117103509500833501501108503501109502501801501503102509509601Database name: Ch07_ConstructCo| EMP_INITIAL|EMP_HIREDATE | JOB_CODE | EMP_YEARS008-Nov-00 50212-4-89 501HEK01-Dec-96 50015-Nov-87 50101-Feb-93 502K502501601502503509DCIMO XEOXSCOTable name: ASSIGNMENTASSIGN UM ASSION DATE PROJNUM EM UM ASSION JOB ASSIGN CHO HASSION HOURS ASSIGN CHARGE100122--10 1014335117.100210031004100510061007100810091010101122MM-10 2222-M-15 1822-a-10 1822-MM-15 2522-Mar-10 2222-M-10 2522-16 1823-Mar-18 1523-am-10 1523-3M-15 2523-10 1823-ar-18 2523-16 2223-M-10 1523--18 22234-10 1810121013101410151016101710181019103010211022102310341025BWAMHJ04534.5534.5504.596.75957593.7584595.7534.6510591.7596.7596.7504.510534.5534.5591051259105110522-Jun-04 50010-0d-93 60012584534.5522-Aug-91 50116-A4-97 50111-Jun-OR 4011-Dec-95 505MAE91 5004-Apr-91 50623-Oct-94 50715-Nov-95 50820-Aug-93 50025-Jan-82 50105-Mar-97 51019-Jun-96 50904-Jan-05 5102592242381.958244.3342286.1473.0CEEEEEEEE3.149353342296.75145.11091490.56212.06405.3510076.05541 882.9245153309.9519352709$15.454936131.2976.01541.45367 5290.35541.454375270.05145.1117202131025n13E2426520m22V2723221242425212113

Answer : a) Create table syntax : Create table table_name (column1 datatype , colunmn2 datatype…

Using Figure P7.1 in the text as a guide, write the SQL/DDL to create the Job and EMPLOYEE tables. Note the cardinality requirements for the relationship indicates a foreign key and values are required. Write the SQL commands to add the first three records to the JOB table and the first three records to the EMPLOYEE table. a. What happens if you create the EMPLOYEE table before the JOB table? Why? b. What happens if you add an employee record with a JOB _CODE not present in the JOB table? Why?

Using Figure P7.1 in the text as a guide, write the SQL/DDL to create the Job and EMPLOYEE tables. Note the cardinality requirements for the relationship indicates a foreign key and values are required. Write the SQL commands to add the first three records to the JOB table and the first three records to the EMPLOYEE table. a. What happens if you create the EMPLOYEE table before the JOB table? Why? b. What happens if you add an employee record with a JOB _CODE not present in the JOB table? Why?

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: Just let me know what to write for the SQL theory: Question: Write a query to display the first…

A: Solution: Given,

Q: Write SQL statements to (1) create the BREED table, (2) insert the data in Figure 3-21 into the…

A: Given: Write SQL statements to (1) create the BREED table, (2) insert the data in Figure 3-21 into…

Q: Employee (empNo, firstName, lastName, address, Department (deptNo, deptName, mgrEmpNo) Project…

A: Solution- We have create an query for each of them in step by step manner.Here, we look for patterns…

Q: What is the SQL command to list the number of product sales (number of rows) and total sales by…

A: SELECT TM_MONTH, P_CATEGORY, COUNT(*) AS NUMPROD, SUM(SALE_UNITS*SALE_PRICE) AS TOTSALES FROM…

Q: I need help figuring out the SQL quereys for the questions above using table joining method. 1)…

A: Please refer to the following step for the complete solution to the problem above.

Q: You are working with a database table that contains invoice data. The table includes columns for…

A: Solution : Use DISTINCT operator in the SELECT clause to remove duplicate rows from the result set.…

Q: Create the following tables in your database with the following schema: Table: Employee +- | Column…

A: Here are all the queries asked in above:

Q: Open the StateSortQuery in SQL View. Modify the query to sort the records in ascending order by the…

A: Assuming that the StateSortQuery SQL query already exists, the modified query to sort the records in…

Q: How many SQL queries change the structure of the table? Which significant alterations must to be…

A: The answer is given below

Q: How many different SQL queries do you think changed the table's structure? Which substantial…

A: The answer is given below step.

Q: use sql to Display customer Id, customer name and total number of orders for customers

A: As the table name is not mentioned in the question, assume the name of the table is Customer_info.…

Q: insert the tasks in a batch into the database. In other words, they want to insert the rows all…

A: According to given answer , value will inserted one by one. So below i write code to insert two rows…

Q: Write a SQL query. List names of all employees who have sold to customer Rachel Patterson (Note: You…

A: In this database problem, we are tasked with creating an SQL query that lists the names of all…

Q: If you have 100 bottles and you need to put them in cases that will hold 12 bottles each, how should…

A: The solution is given below with explanations

Q: SELECT BRAND_NAME, BRAND_TYPE, round(AVERAGE,2) AS 'Average Price', UnitsSold FROM (LGBRAND INNER…

A: Actually, SQL stands for structured query language.

Q: Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle,…

A: In the given scenario, we have a table named PRODUCT with attributes like ProductID, ProductName,…

Q: Write the SQL to build the PRODUCT and PURCHASE_ORDER tables, as shown in the picture. Use your…

A: The SQL language is used for managing and manipulating data in a relational database management…

Concept explainers

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.

Question

100%

Using Figure P7.1 in the text as a guide, write the SQL/DDL to create the Job and EMPLOYEE tables.
Note the cardinality requirements for the relationship indicates a foreign key and values are required.
Write the SQL commands to add the first three records to the JOB table and the first three records to
the EMPLOYEE table.
a. What happens if you create the EMPLOYEE table before the JOB table? Why?
b. What happens if you add an employee record with a JOB _CODE not present in the JOB table?
Why?

Relational diagram
106
Pb Cade
500
501
002
500
504
505
506
508
5001
510
Job Description
ob Che Hou
ob Last Update
Table name: JOB
Joe CODE JOB DESCRIPTION JOB CHO HOUR JOB LAST UPDATE
20.Nov-17
20-Nov-17
24--18
15
10
22
25
Programmer
Svitent Analyst
EMPLOYEE
Ema Num
Emp Name
Emp ame
Ema Initial
Emp, MireDate
FES Code
Emp Years
Date Designer
Dectrical Engineer
Mechanical Engineer
Civil Engineer
Clerical Support
DSS Anwyst
Applications Designer
Bo Technician
General Support
-H---
Evergreen
Amber Wave
Rolling Tide
Starfight
35.75
96.75
Table name: PROJECT
PROJ NUM PROJ NAME PROJ VALLE
1453600.00
350050000
805000.00
265050000
12500
84.50
67 90
55.78
2687
45.96
45.10
34.55
10.30
ASSIGNMENT
PAsien m
Assign Date
FKZP
Nem
FC EmpNam
EMP
Aug
Ai Che --H-PLN
Assign Meurs
Assign, Charge
20-Nov-17
20-May-17
20-Nov-17
20-Nov-17
20-Nov-17
24-Mar-18
20-Nov-17
20-Nov-17
FROU BALANCE EMP NUM
1002350 00 103
2110346.00 100
500345 20 102
2309000.00 107
PROIECT
Prej Name
Pre Value
Prej Balance
FR3 Song Num
Table name: EMPLOYEE
| EMP_NUM | EMP_UNAME | EMP_FNAME
101 News
John
Senior David
Arbough June
102
Ramoras Anne
Johnson Alce
Willian
Smithfield
Abonzo
Marin
Maria
Washington
Ca
Smith
103
104
104
105
106
107
100
109
an
110
***
111
m
112
F
113
114
114
115
116
117
118
Clerko
visbath
Smithson
Terrood
s
Jones
Bawang
Pri
Geoff
Darlen
Darlene
Debert
Annelise
Travis
Gerald
Willianson Angie
Frommer Janes
23-Mar-18 25
24-3-10 25
24-15 15
24-Mar-18 22
24-at-10 22
24-10 22
24--16 15
24-3-10 18
117
163
100
104
113
103
115
117
105
100
115
104
163
105
112
117
104
201
Ralph
Larry
P
Gerald
Tom
105
115
105
183
117
103
509
500
833
501
501
108
503
501
109
502
501
801
501
503
102
509
509
601
Database name: Ch07_ConstructCo
| EMP_INITIAL|EMP_HIREDATE | JOB_CODE | EMP_YEARS
0
08-Nov-00 502
12-4-89 501
H
E
K
01-Dec-96 500
15-Nov-87 501
01-Feb-93 502
K
502
501
601
502
503
509
D
CIMO XEOXSCO
Table name: ASSIGNMENT
ASSIGN UM ASSION DATE PROJNUM EM UM ASSION JOB ASSIGN CHO HASSION HOURS ASSIGN CHARGE
1001
22--10 10
143
35
117.
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
22MM-10 22
22-M-15 18
22-a-10 18
22-MM-15 25
22-Mar-10 22
22-M-10 25
22-16 18
23-Mar-18 15
23-am-10 15
23-3M-15 25
23-10 18
23-ar-18 25
23-16 22
23-M-10 15
23--18 22
234-10 18
1012
1013
1014
1015
1016
1017
1018
1019
1030
1021
1022
1023
1034
1025
B
W
A
M
H
J
045
34.55
34.55
04.5
96.75
9575
93.75
845
95.75
34.65
105
91.75
96.75
96.75
04.5
105
34.55
34.55
9105
125
9105
1105
22-Jun-04 500
10-0d-93 600
125
845
34.55
22-Aug-91 501
16-A4-97 501
11-Jun-OR 40
11-Dec-95 505
MAE91 50
04-Apr-91 506
23-Oct-94 507
15-Nov-95 508
20-Aug-93 500
25-Jan-82 501
05-Mar-97 510
19-Jun-96 509
04-Jan-05 510
2
59
22
42
38
1.9
58
24
4.3
34
2
28
6.1
47
3.0
CEEEEEEEE
3.1
49
35
33
42
296.75
145.11
091
490.56
212.06
405.35
100
76.05
541 8
82.92
4515
3309.95
1935
2709
$15.45
4936
131.29
76.01
541.45
367 5
290.35
541.45
4375
270.05
145.11
17
20
21
310
25
n
13
E
24
26
5
20
m
22
V
27
23
2
21
24
24
25
21
21
13

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step by step

Solved in 6 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Three tables are given Student: studentNO, firstName, lastName, studentProgram Professor: professorID, professorProgram, professorName Student_professor: studentNO, professorID, mentor Complete the following requirements: Using the UNION operator, do a full outer join to display the student last name student program, professor name and professor program. Referring to the model created in step 2, determine the PK/FK relationships to build the SQL joining the three Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join.
Write following SQL Queries:1. List the order number, order date, customer number, customer name (first and last), employee number, and employeename (first and last) of January 2017 orders placed by Colorado customers.2. List the customer number, name (first and last), order number, order date, employee number, employee name (first andlast), product number, product name, and order cost (OrdLine.Qty * ProdPrice) for products ordered on January 23,2017, in which the order cost exceeds $150.3. List the order number and total amount for orders placed on January 23, 2017. The total amount of an order is the sumof the quantity times the product price of each product on the order.4. List the order number, order date, customer name (first and last), and total amount for orders placed on January 23,2017. The total amount of an order is the sum of the quantity times the product price of each product on the order.
Please answer as many questions as you can!
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
(a) Write the sql syntax to create a table name Client with the following restrictions; Client(cid, cname, address, age, occupation, salary, payment_mode) Where cid is the primary key. cname do not accept null values. Payment_mode accepts only cash or credit entries. The default address is “vacoas". client is at least 25 years old. Salary is above Rs 30000. (b) Consider the schema of Customer table in part(a) and write SQL statements for the following queries. i. Using SQL, write the command to add a new column in the client table with the following data definition; (Column “gender" of char data type and field size of 1). ii. Using SQL, write the command to insert the values in the Client table. cid address age occupation salary Payment_mode gender cname 123 sam vacoas 31 manager 60000 cash m 124 david flacq 38 administrator 45000 credit m iii. Write the SQL query to display the cid of all employees. iv. Write the SQL code to list all administrators whose salary is greater than Rs…
Part B Create the entities, subtypes, and relationships according to the business rule below. Then map the entities identified into tables. Do these in SQL developer. BUSINESS RULE: A Seller can be categorized as Retailer or Wholesaler. All sellers have a seller id, first name, last name, contact no and maximum credit. A retailer has a RetailPrice, while a Wholesaler has a WholeSalePrice. Each seller should be categorized only to either a retailer or a wholesaler and cannot be a retailer and a wholesaler at the same time. A seller can have one or more shops and shops must be owned by a seller. A shop can contain many products and many products must be found in at least one shop or more shops. Products Shops price type manufacturer Kaddress name Copy and Paste your answers here. (Answers should come from SQL developer)
4. This question continues from Q3. (a) Write the insert SQL to populate the Zoom, BB, and the QR tables with the values given below. For each table, use only one insert to complete the insert task. The Duration column is initially empty. (b) Can you insert the ZOOM table first? Provide a reason according to the relational database theory if it is not possible. (c) Write a Delete SQL to remove student ‘M M’ from the BB table. If it is not possible to remove the row, provide a reason according to the relational database theory. You must not use SID in the SQL because only the name is given. (d) Write an Update DML that will compute and update the duration in minutesin the Zoom table. Show the updated table and the DML.
USE this part to write SQL statements at the bottom questions 1-11 Branch(branch_id:integer, branch_name:varchar(50), branch_location:varchar(40), money_on_hand:numeric(15,2) create table Branch (branch_id integer, branch_name varchar(50), branch_location varchar(40), money_on_hand numeric(15,2), primary key (branch_id)); Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2)) foreign key branch_id references Branch(branch_id) create table Loan (loan_number integer, branch_id integer, amount numeric(8,2), primary key (loan_number), foreign key (branch_id) references Branch (branch_id)); Customer(customer_id:integer, customer_last_name:varchar(35),customer_first_name:varchar(25), customer_street:varchar(30),…
I want to write the SQL to create the tables for the ERD attached, but I also want to add this to the tables: rental date, due date, and return date so that I can support overdue queries. I also want it so each table has primary keys and foreign keys. I want it so the tables have at least 4 rows of data. How can I do this? Thank you for your help as I learn more about SQL and table creation.
use EMP and DEPT tables, in the Live SQL, to perform the following operations. 1- List the Departments's Names that have no employees 2- List the name of the employees' Names, dept's Names, and the SAL in JD rounded to 2 digits (one dollar = 0.7 JD). EMP and DEPT tables: https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html
You are working with a database table that contains invoice data. The table includes columns for invoice_id and billing_state. You want to remove duplicate entries for billing state and sort the results by invoice ID. You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the billing_state column. NOTE: The three dots (...) indicate where to add the clause. SELECT ... FROM invoice ORDER BY invoice_id What billing state appears in row 17 of your query result? 1 point 1-CA 2-NV 3-WI 4-AZ
Please show all work and write it in PL/SQL and copypaste what you have.