Levels Tought:
Elementary,Middle School,High School,College,University,PHD
Teaching Since: | Apr 2017 |
Last Sign in: | 5 Weeks Ago, 4 Days Ago |
Questions Answered: | 4870 |
Tutorials Posted: | 4863 |
MBA IT, Mater in Science and Technology
Devry
Jul-1996 - Jul-2000
Professor
Devry University
Mar-2010 - Oct-2016
Below is given a Logical Model in the form of an Entity Set that describes only the core of the process for the Warehouse Storage.
Â
REGIONÂ [REGION_ID, REGION_NAME]
Â
DIV      [DIVISION_ID, DIVISION_NAME, REGION_ID (FK)]
Â
WHS   [WAREHOUSE_ID, CITY, rating, FOUND_DATE, DIVISION_ID (FK)]
Â
EMP [  EMPLOYEE_ID, ENAME, JOB, SAL, COMM, DIVISION_ID (FK)]
                                                                                                                           Â
SEC     [WAREHOUSE_ID (FK), SECTION_ID, DESCRIPTION, capacity]
Â
Data already loaded into the tables are shown on the next page
Â
Â
Â
> Select * From WHSE;
WAREHOUSE_ID CITYÂ Â Â Â Â Â Â Â Â Â Â RATING FOUND_DATE DIVISION_ID------------ --------------- ------ ---------- -----------Â Â Â Â Â Â Â Â 101 OTTAWAÂ Â Â Â Â Â Â Â Â BÂ Â Â Â Â 12-DEC-98Â Â Â Â Â Â Â Â Â Â 40 Â Â Â Â Â Â Â Â 102 TORONTOÂ Â Â Â Â Â Â Â CÂ Â Â Â Â 13-NOV-97Â Â Â Â Â Â Â Â Â Â 10 Â Â Â Â Â Â Â Â 171 VANCOUVERÂ Â Â Â Â Â AÂ Â Â Â Â 04-APR-98Â Â Â Â Â Â Â Â Â Â Â 3 Â Â Â Â Â Â Â Â 201 CHICAGOÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â 12-AUG-97Â Â Â Â Â Â Â Â Â Â 30 Â Â Â Â Â Â Â Â 202 DETROITÂ Â Â Â Â Â Â Â AÂ Â Â Â Â 11-OCT-98Â Â Â Â Â Â Â Â Â Â 80 Â Â Â Â Â Â Â Â 301 SAN DIEGOÂ Â Â Â Â Â BÂ Â Â Â Â 23-DEC-99Â Â Â Â Â Â Â Â Â Â 60 Â Â Â Â Â Â Â Â 401 BOSTONÂ Â Â Â Â Â Â Â Â CÂ Â Â Â Â 25-JUN-97Â Â Â Â Â Â Â Â Â Â 20 Â Â Â Â Â Â Â Â 402 NEW YORKÂ Â Â Â Â Â Â AÂ Â Â Â Â 08-OCT-98Â Â Â Â Â Â Â Â Â Â 80
Â
 > Select * From SEC;       Â
WAREHOUSE_ID SECTION_ID DESCRIPTIONÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CAPACITY
------------ ---------- ---------------------------------------- ----------
        101         1 For basic maintenance purposes              500000
        101         2 For huge tools and                           80000
        101         3 Office supplies and                       Â
        102         2 Office furniture and                          3000
        102         2 Office furniture and                          3000
        201         1 Electrical Parts                             15000
        401         1 Chemical tools                              20000
        401         2 Various Chemicals                                 Â
 > Select * From EMP       Â
EMPLOYEE_ID ENAMEÂ Â Â Â Â JOBÂ Â Â Â Â Â Â Â Â Â MGR_ID HIREDATEÂ Â Â Â Â SALARYÂ Â Â Â Â Â COMM DIVISION_ID
----------- ---------- --------- ---------- --------- ---------- ---------- -----------
      7839 KING      PRESIDENT      7839 17-NOV-81      5000         0         10
      7698 BLAKE     MANAGER        7839 01-MAY-81      2850         0         30
      7782 CLARK     MANAGER        7839 09-JUN-81      2450         0         10
      7566 JONES     MANAGER        7839 02-APR-81      2975         0         20
      7654 MARTIN    SALESMAN       7698 28-SEP-81      1250      1400         30
      7499 ALLEN     SALESMAN       7698 20-FEB-81      1600       300         30
      7844 TURNER    SALESMAN       7698 08-SEP-81      1500         0         30
      7900 JAMES     CLERK          7698 03-DEC-81       950         0         30
      7521 WARDAR    SALESMAN       7698 22-FEB-81      1250       500         30
      7902 FORD      ANALYST        7566 03-DEC-81      3000         0         20
      7369 SMITH     CLERK          7902 17-DEC-80       800         0         20
      7788 SCOTT     ANALYST        7566 09-DEC-82      3000         0         20
      7876 ADAMS     CLERK          7788 12-JAN-83      1100         0         20
      7934 MILLER    CLERK          7782 23-JAN-82      1300         0         10
      8777 HOLMES    SALESMAN       7698 23-OCT-87      1800      1200         30
Â
> Select * From DIV
DIVISION_ID DIVISION_NAMEÂ Â Â Â Â Â Â Â Â Â Â Â Â REGION_ID
----------- ------------------------- ----------
        10 Admin                             3
        20 Chem                              2
        30 Maint                             2
        60 Mech                              1
        80 Misc                              4
Â
Â
Â
Â
1 Create a table called REG (meaning Region).
It will have columns REGION_ID and REGION_NAME.
Region id will be a PK done at the table level with a standard proper naming used.
Region name will be NN with no naming needed and done at the column level.
Â
SQL here
Â
Â
Â
Â
2 SQL to show the structure of the table you just created and show the result from that SQL statement.
Â
Â
Â
Â
3 For table SEC add the PK and FK
SQLÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
Â
Â
Â
Â
Â
4 Add a new row of data to the SECTION table for Section 3 in Warehouse 402. For the description use your name and capacity 7000.
SQL here:
Â
Â
Â
Â
Â
5 Do a select that shows all rows and columns in SECTION.
SQL here:
Â
Â
Â
Â
Â
6 Create a sequence for WHSE that will start at 500 and increment by 5 to a maximum of 730. Do not store the values in memory.
SQL here:
Â
Â
Â
Â
7 Add the following rows of data to WHSE
Â
warehouse_id  city rating       Found_date Division_id
Â
Hamilton nothing   Use current date 60
Â
           Niagara Falls   A Use current date 60
Â
Quebec G Use current date 60
Â
SQL here:
Â
Â
Â
8 Delete the all rows that have a rating of A.
SQL only here:
Â
Â
Â
Â
9 Update all rows with a rating of B to A.
SQL only here:
Â
Â
Â
10 Show all rows in WHSE and all columns.
SQL here:
Â
Â
Â
Â
11 Create a table called MANAGERS from the EMP table that contain only those with a JOB called 'Manager'. Do not create the table from scratch.
SQL here:
Â
Â
Â
Â
Â
12 Show the select * results on table MANAGER,
SQL here:
Â
Â
Â
Â
13 Create view that shows
- manager name
- department name of manager
- employee name
- department name of employee
Â
SQL here for the view:
Â
Â
Â
Â
Â
Â
Â
14 SQL to show results of executing the view:
SQL here:
Â
15 If you want to modify the view what is the first line of the command.
Â
Â
Â
Â
16 After a lot of testing for a new system the computer specialist discovered 2 tables called EMP1 and EMP2. What they want is a list of any duplicates. Just state the one-word key name in the join.
Â
Â
Â
17 Same tables as in question 15, but only want to see the ones unique to EMP2. Again what is the one-word key name.
Â
Â