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 up a new sales order for each product sold.
Develop the following queries and submit them as a picture or document:
a. All the Sales People with less than three years in position who have sold Product Number 1167.
b. All the Products with the word “bracket” in the Product Name.
c. All the Sales Orders for products with a price of more than $33.
a. Select SalesPeople.EmployeeNumber from SalesPeople, SalesOrder where SalesPeople.EmployeeNumber = SalesOrder.EmployeeNumber and SalesPeople.YearsInPosition < 3 and SalesOrder.ProductNumber = 1167
In this case we have joined the SalesPeople and SalesOrder table and used the required conditions.
b. Select ProductDescription.ProductNumber from ProductDescription where ProductDescriptuon.ProductName like '%bracket%'.
Product number with product name containing bracket is displayed.
c. Select SalesOrder.SalesOrderNumber from SalesOrder, ProductDescription where SalesOrder.ProductNumber = ProductDescription.ProductNumber and ProductPrice > 33
Sales order and Product description table are joined and the required conditions are applied.
Please do it by type not pics. 1.Consider the following three tables, primary and foreign keys....
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...
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,...
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...
Give an example of 3 relations illustrating primary keys and foreign keys. Consider the following relations: Student(sID, surName, firstName, campus, email) Course(dept, cNum, name) Offering(oID, dept, cNum, term, instructor) Took(sID, oID, grade) Such as: Offering[dept, cNum] ⊆ Course[dept, cNum] Took[sID] ⊆ Student[sID] Took[oID] ⊆ Offering[oID] Answer the following query using relational algebra -Give the Student number of all students who have taken the course number “343” by the department “CS”. - Find sID of all students who have earned some...
Consider the following relational database to manage concert and ticket sales. The relations are artist, concert, venue, seat, ticket, and fan. The schemas for these relations (with primary key attributes underlined) are: Artist-schema = (artistname, type, salary) Concert-schema = (artistname, date, venuename, artistfees) Venue-schema = (venuename, address, seating_capacity) Seat-schema=(venuename, row, seatnumber) Ticket-schema = (fanID, date, venuename, row, seatnumber) Fan-schema = (fanID, name, address, creditcardno) Where: • artistname is a unique name for the artist (because of trademark/copyright rules no two...
Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4 Orders(orderld, customerld, dateOrdered, dateRequired, status) Customer(customerld, customerLastName, customerStreet, customerCity, customerState, customer Lip OrderDetails(orderld.productld, quantity, lineNumber, amount) Products(productld, name, description, quantity, unitPrice) Account(accountNumber, customerld, dateOpened, creditCard, mailingStreet, mailingCity, mailingState, mailingZip) 2.1 (2 Points) List all possible foreign keys. For each foreign key list both the referencing and referenced relations. 2.2 (2 Points) Devise a reasonable database instance by filling the tables with data of...
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...
Please help I promise positive feedback Part IV. Formulate SQL queries that follow for the database with the following tables: CUSTOMER, PRODUCT, SALES, and ITEM-SOLD. Table: CUSTOMER Primary Key: Account-No Account-No Customer-Name Customer-City 1 A NYC 2 B NYC 3 C MIA Table: PRODUCT Primary Key: Item-No Item-No Price 1 $1.00 2 $2.00 3 $3.00 4 $4.00 Table: SALES Primary Key: Receipt-No Foreign Key: Account-No References CUSTOMER Receipt-No Account-No Sales-Person 1 1 S1 2 1 S1 3 2 S2 4...
using SQL Assume the relations shown below (underlined attributes are primary and foreign keys): merchants(mid, name, city, state) products(pid, name, category, description) sell(mid, pid, price, quantity_available) orders(oid, shipping_method, shipping_cost) contain(oid,pid) customers(cid, fullname, city, state) place(cid, oid, order_date) For each of the following questions write a SQL query: List names and sellers of products that are no longer available (quantity=0) List names and descriptions of products that are not sold. How many customers bought SATA drives but not any routers between 2009...
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,...