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

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.
Using the Schema - data/statistics Write down the following SQL queries. Assume appropriate data types for each attribute by yourself. 1) Create Bill table. 2)Assume you forgot to set SID as foreign key during creation of Bill table, by default SID is declared as null.Now you have to declare SID as foreign key. 3)Add constraint on names of customer that ensures that names must start with alphabet. 4)Delete constraint that was added in statement 3. 5)Add a new column in any one of the tables. 6)Delete the added column in statement 5 7)Show name of all those customers who have placed order after 23 June 2014 using subquery. 8)Show the data of those customers to who items were sold with SID 1 or 3 or 6 9)Show names of customers and items names of items sold to him 10)Make a vertical partition on customer table in such a way that changes in partitioned portion must be reflected on original table. 11) Update the city of customer as ‘Lahore’ where customer id is 3 12)Delete all those records…
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
QY Consider the following relations: BRANCH (branch no, street, area, city, degree, Tel_no, Fax_no) STAFF (Staff_no, Frame, Luame. address. position, salary. branch_no) Write the following queries in SQL: 1. Find all the branches that number less than 453 and found in area ‘34 east’ 2. Find staff whose salary is greater than the salary of every member of staff at branch 71 3. List all stlf who their last name ends with ‘it 4. Find allthe staff that works in branch 345 and have salary between 15000 and 45000.
(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