Consider the following three tables, primary and foreign keys.
Table Name
SalesPeople
Attribute Name Type Key Type
EmployeeNumber Number Primary Key
Name Character
JobTitle Character
Address Character
PhoneNumber Character
YearsInPosition Number
Table Name ProductDescription
Attribute Name Type Key Type
ProductNumber Number Primary Key
ProductName Character
ProductPrice Number
Table Name SalesOrder
Attribute Name Type Key Type
SalesOrderNumber Number Primary Key
ProductNumber Number Foreign Key
EmployeeNumber Number Foreign Key
SalesOrderDate Date
Assume that you draw up a new sales order for each product sold.
Develop the following queries in SQL:
a. All the Sales People with more than five years in position. (3 pts)
b. All the Products that cost more than $50. (3 pts)
c. All the Sales Orders sold by Sales People less than 3 years in the position. (3 pts)
Consider the following three tables, primary and foreign keys. Table Name SalesPeople Attribute Name  
Please do it by type not pics. 1.Consider the following three tables, primary and foreign keys. Table Name SalesPeople Attribute Name Type Key Type EmployeeNumber Number Primary Key Name Character JobTitle Character Address Character PhoneNumber Character YearsInPosition Number Table Name ProductDescription Attribute Name Type Key Type ProductNumber Number Primary Key ProductName Character ProductPrice Number Table Name SalesOrder Attribute Name Type Key Type SalesOrderNumber Number Primary Key ProductNumber Number Foreign Key EmployeeNumber Number Foreign Key SalesOrderDate Date Assume that you draw...
SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address has been denormalized to include City and State for performance reasons. DeptNbr is a foreign key that references DeptNbr in the Department table Manager is a foreign key that references EmployeeID in the Employees table Data Structure: (EmployeeId, LastName, FirstName, Street, City, State, Zip, DateOfHire, DateOfBirth, JobTitle, Salary, DeptNbr(fk), Manager(fk)) TABLE: Department Business Rules: DeptNbr is defined as the primary key. Data Structure: (DeptNbr,...
these are all tables please see the tables and questions are mentioned below please see that all and I need answers asap please write proper answer it's an easy task and don't take much time please do it fast thanks in advance EMPLOYEE Remark Column Name EmployeeNumberINT Primary Key Yes No CHAR (25 CHAR (35 CHAR 25 NUMERIC INT CHAR (12 CHAR Name in the DEPARTMENT table Position No Number in the EMPLOYEE table Su OfficePhone EmailAddress No No No...
IN SQL Hotel Reservation Room Table Attribute name ATTRIBUTE DESCRIPTION Domain Range pk FK NULL Room Number Yes NO NO No Haddress location No No No No Yes type The number of the room primary INTEGER >0 key Hotel address VARCHAR(50) any The exact location of the room; VARCHAR(30) Any north tower, south tower... Type of room; big, small or VARCHAR(25) any medium The date that the room was DATE ANY reserved Whether or not the room is BOOLEAN ANY...
Write SQL Queries Given the following tables (7pts) Retrieve the names of employees and their project name. If the employee never worked on a project, show the names only the name, not the project name. (7pts) Retrieve the names of employees who have worked on the same project at a different location. (7pts) Retrieve the names of employees who have worked on more than two different projects. (7pts) Retrieve the names of employees who manage more than two employees. CREATE...
Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys Exercise In Lab 09, we will continue the practice of creating tables. You will need to write SQL statements for the tasks specified below. After you complete them, submit your SQL script (in one *.sql file) to Blackboard by 11:59pm Nov. 16 (Friday). Include your name as a comment in the SQL script submitted. The creation statement for each table is worth one point,...
1) True or False - The foreign keys in a table are linked to the primary keys in another tables 2_ When we convert this ER to relations, which relation will represents the check entity: Check( account_number,check_number, check_date ,recipient,amount,memo) Check( check_number, check_date ,recipient,amount,memo) Check( check_date ,recipient,amount,memo) Check( account_number, check_date 3) When we convert this ER to relation, we will have: hint: address is multi-valued attribute Question 4 options: customer(cust_id,name) address(cust_id,street,city,state,zipcode) customer(cust_id,name, street,city,state,zipcode) customer(cust_id,name) address(cust_id,street,city,state,zipcode) check date account number recipient balance...
Consider the following table schemas (primary keys are underlined): Book (BookId, Title, Publishername) Book_Authors (BookId, AuthorName) Publisher (Name, Address, Phone) Book_Copies (BookId, BranchId, No_Of_Copies) Book_Loans (BookId, BranchId, CardNo, DateOut, DueDate) Library_Branch (BranchId, BranchName, Address) Borrower (CardNo, Name, Address, Phone) Write SQL statements for the following queries: List the number of copies of the book titled “The Lost Tribe” owned by each library branch and the corresponding library branch name. List the names and addresses of all borrowers who have borrowed more than 5 books...
create table candidate ( cand_id varchar(12) primary key, -- cand_id name varchar(40) -- cand_nm ); create table contributor ( contbr_id integer primary key, name varchar(40), -- contbr_nm city varchar(40), -- contbr_city state varchar(40), -- contbr_st zip varchar(20), -- contbr_zip employer varchar(60), -- contbr_employer occupation varchar(40) -- contbr_occupation ); create table contribution ( contb_id integer primary key, cand_id varchar(12), --...
Exercise 3 [12.5 marks] Given the Restaurant management system database that contains the following tables (primary keys are underlined and foreign keys are preceded with #): Customer (customerID,customerFirstName,customerLastName,customerAddress) Oreder (orderID,orderDate, #customerID,#menuItemID,#staffID) MenuItem(menuItemID, menuItemName,ingredients,type,availability) Staff(staffID, staffName, staffPhoneNumber, staffRole ) OrderPayment(paymentID,paymentAmount,#orderID,#staffID) 1) Without using DISTINCT, write the SQL query equivalent to the following one:[1.5 marks] SELECT DISTINCT menuItemName FROM MenuItem WHERE type = ‘Vegetarian’ OR availability= ‘Yes’; 2) Express the following queries in SQL: a) Find the number of orders placed by...