Question

In: Computer Science

Consider the following three tables, primary and foreign keys. Table Name        SalesPeople Attribute Name                  &nbsp

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 less than four years in position.

b.      All the Product Names sold on April 4th.

c.       All the Products sold by Sales People less than 3 years in the position.

Solutions

Expert Solution

Answer:

a.All the Sales People with less than four years in the position.

Select * from SalesPeople where YearsInPosition < 4;
/* This command will list down all the Sales People with less than four years in position*/

b.All the Product Names sold on April 4th.

Select so.SalesOrderDate, pd.ProductName from SalesOrder so
join ProductDescription pd on pd.ProductNumber = so.ProductNumber
where so.SalesOrderDate like '04/04%';

/* This command will list down all the Product Names sold on April 4th */

c.All the Products sold by Sales People less than 3 years in the position.

Select so.ProductNumber, sp.Name, so.SalesOrderNumber, sp.YearsInPosition from SalesOrder so
join SalesPeople sp on sp.EmployeeNumber = so.EmployeeNumber
where sp.YearsInPosition < 3 order by sp.Name;

/* This command will list down all the Products sold by Sales People less than 3 years in the position.*/

Let me know in case if you have any doubts. Thanks and All the best.


Related Solutions

Consider the following three tables, primary and foreign keys. Table Name        SalesPeople Attribute Name                  &nbsp
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...
1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are...
1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are in italic and bold). Make sure you have all needed constraints and appropriate datatypes for attributes: Student (stID, stName, dateOfBirth, advID, majorName, GPA) Advisor (advID, advName, specialty) 2.  Insert several records in each table.
1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are...
1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are in italic and bold). Make sure you have all needed constraints and appropriate datatypes for attributes: Student (stID, stName, dateOfBirth, advID, majorName, GPA) Advisor (advID, advName, specialty) 2. Insert several records in each table.
Consider the following relational schema about a University (the primary keys are underlined and foreign keys...
Consider the following relational schema about a University (the primary keys are underlined and foreign keys are italic) STUDENT(StudentID, name, major, year, age) CLASS(ClassName, meetsAt, room, LecturerID) ENROLLED(StudentID, ClassName, mark) LECTURER(LecturerID, name, DepartmentID) DEPARTMENT(DepartmentID, name) Write the SQL statements for the following query: B1. Find the age of the oldest student. B2. Find the ID’s of lecturers whose name begins with “K” \ B3. Find the age of the youngest student who is enrolled in Mechatronics. B4. Find the age...
Please create the following tables for a tool rental database with appropriate primary keys & foreign...
Please create the following tables for a tool rental database with appropriate primary keys & foreign keys. Assumptions: 1. Each tool belongs to a category. 2. Each category may have a parent category but the parent category should not have a parent category (so at most two levels). E.g., a Tool A belongs to the electric mower, and electric mowers belong to mowers. Mower has no parent category. 3. Each tool can be rented at different time units. The typical...
Consider the following relational schema (the primary keys are underlined and foreign keys are italic) ITEM(ItemName,...
Consider the following relational schema (the primary keys are underlined and foreign keys are italic) ITEM(ItemName, ItemType, ItemColour) DEPARTMENT(Deptname, DeptFloor, DeptPhone, Manager) EMPLOYEE(EmpNo, EmpFname, EmpSalary, DeptName, SupervisedBy) SUPPLIER(SupNo, SupName) SALE(SaleNo, SaleQty, ItemName, DeptName) DELIVERY(DeliNo, DeliQty, ItemName, DeptName, SupNo) Write the SQL statements for the following queries: C1. Find the names of items sold on first and second floors. [1 mark] C2. For each department, list the department name and average salary of the employees where the average salary of the...
1. Write CREATE TABLE statements for the following tables (foreign keys are in italic and bold)....
1. Write CREATE TABLE statements for the following tables (foreign keys are in italic and bold). Make sure you have all needed constraints and appropriate datatypes for attributes: Student (stID, stName, dateOfBirth, advID, majorName, GPA) Advisor (advID, advName, specialty) 2.  Insert several records in each table.
Create the following tables. The underlined bold column names are the primary keys. Make sure to...
Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements. customer: (cus_code:integer, cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char, cus_areacode:integer,cus_phone:integer). invoice: (inv_number:integer, cus_code:integer, inv_date:date,                   foreign key cus_code references customer(cus_code)) vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30),vend_areacode:integer,vend_phone:integer) product:(prod_code:integer, prod_desc:varchar(50), prod_price:integer, prod_quant:integer,vend_code:integer, foreign key (vend_code) referenecs Vendor(vend_code)) line: (inv_number:integer, prod_code:integer ,line_units:integer, foreign key (inv_number) references Invoice(inv_number), foreign key (prod_code) references Product (prod_code) )
Create a Database Schema for a hotel reservation system. indicate the Primary Keys, Foreign Keys, and...
Create a Database Schema for a hotel reservation system. indicate the Primary Keys, Foreign Keys, and the one-to-one or one-to-many relationships between the tables. Also describe in a small paragraph the scope of the back-end database, by explaining the different tables and their relations in your schema.
Problem 1. Please create the following tables for a tool rental database with appropriate primary keys...
Problem 1. Please create the following tables for a tool rental database with appropriate primary keys & foreign keys. [30 points] Assumptions: Each tool belongs to a category. Each category may have a parent category but the parent category should not have parent category (so at most two levels). E.g., a Tool A belongs to electric mower, and electric mower belongs to mower. Mower has no parent category. Each tool can be rented at different time units. The typical time...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT