Name: _____________________________________    Student ID: ________________

             (In Blocks Letters)

Good Luck!

Write a SQL Statement to create the following table (5)You query the database with this command:

SELECT name

FROM employee

WHERE name LIKE ‘_a%’;

Which names are displayed?

names starting with “a”

names starting with “a” or “A”

names containing “a” as the second letter

names containing “a” as any letter except the first

ANSWER: ___

What happens when you update a table without a WHERE clause?

A The statement will not execute

B. Only the rows specified will be updated

C. All of the rows in the table will be updated

D. The statement will execute, but no changes will be made

ANSWER: ___

In which situation would it be most appropriate to use a self join query?

(Self join is joining a table to itself (think recursive relationship).

The EMPLOYEE and REGION tables have corresponding columns

The EMPLOYEE and REGION tables have no corresponding columns

The EMPLOYEE table has two columns that correspond to each other

The EMPLOYEE table column corresponding to the REGION table column contains null values for rows that need to be displayed.

ANSWER: ___

Which TWO relationships exist for PATIENT and DOCTOR if a patient can have many doctors, a doctor can have many patients, and a doctor can be a patient?  (CHOOSE TWO) 

recursive

one-to-one

one-to-many

many-to-one

many-to-many

ANSWERS: _____

Build a ER Diagram to handle the library described below. ( 15 Points )

The library handles books. Books have a title, author, ISBN, and additional relevant data you think are needed. Books are identified by a barcode. There can be many copies of the same ISBN but only one copy of an actual book per barcode. These books reside in the library on shelf locations. A shelf location is made up of the aisle, row, cabinet, and shelf numbers. A book can have only one location, but shelves are wide and can contain many books. Some shelves may be left empty on purpose. The library lends books to patrons. A patron is identified by an ID. Additional information about each patron, including name, address, telephone numbers, and email addresses must also be kept. A patron can have many telephone numbers and emails. The library keeps track of which books have been lent to each patron, when the books were lend out, when they were returned. Also keep track of the categories of each book. A book can have many categories, such as MIS, Biology, Literature, etc., but must have at least one.

Q4   Consider the following table employee. Write a sql statement that list the employee and his/her corresponding Supervisor? (10pts)

The sample Output should be in the following format:

Supervisor                      Employee

----------------------------  -------------------

Bordoloi, Bijoy              Joyner, Suzanne

Bordoloi, Bijoy              Zhu, Waiman

Joyner, Suzanne             Markis, Marcia

Q) Please use the following GARDEN database to answer questions. ( 20 Pts)

               Location                        Gardener

               --------                        --------

LocationID   Name   Sunlight  Water  | GardenerID  Name    Age  

----------   ----   --------  -----  | ----------  ----    ---

    0        East     .28      .80   |    0        Mother   36

    1        North    .17      .84   |    1        Father   38

    2        West     .38      .48   |    2        Tim      15

    3        South    .45      .66   |    3        Erin     12

                Plant                       

                -----                        

PlantID    Name   Sunlight   Water   Weight

-------    ----   --------   -----   -----

    0      Carrot    .26      .82    .08

    1      Beet      .44      .80    .04

    2      Corn      .44      .76    .26

    3      Tomato    .42      .80    .16

    4      Radish    .28      .84    .02

                    Planted

                    -------

PlantFK  GardenerFK  LocationFK    Date     Seeds

-------  ----------  ----------    ----     -----

   0         0           0     04-18-2005    28

   0         1           1     04-14-2005    14

   1         0           2     04-18-2005    36

   2         1           3     04-14-2005    20

   2         2           2     04-19-2005    12

   3         3           3     04-25-2005    38

   4         2           0     04-30-2005    30

               Picked

               ------

PlantFK  GardenerFK  LocationFK    Date    Amount   Weight

-------  ----------  ----------    ----    ------   ------

   0         2           0     08-18-2005    28      2.32 

   0         3           1     08-16-2005    12      1.02

   2         1           3     08-22-2005    52     12.96

   2         2           2     08-28-2005    18      4.58

   3         3           3     08-22-2005    15      3.84

   4         2           0     07-16-2005    23      0.52

Some notes on terms:

- The database is for a simple garden kept by a small family

- They plant their garden in the spring and pick their garden in summer

- The picked amount is the number of items (one carrot, one beet, an ear of

  corn, one tomato, one radish) picked.

Write a valid SQL statement to drop the Age column in the Gardner table: 

For some reason, the planted table is incorrectly populated and we need to get rid of all the data in the table

   Write a valid SQL statement to delete all rows in the Planted table:

Write a valid SQL statement that calculates the total weight of all ears of corn that were picked from the garden

Write a valid SQL statement that would produce a result set like the 

    following:

      Gardner | Plant  |    date    | amount 

        ------+--------+------------+--------

         Tim  | Radish | 2005-07-16 |     23

         Tim  | Carrot | 2005-08-18 |     28

         Tim  | Corn   | 2005-08-28 |     18

BONUS QUESTION:                                                                                    

Consider the following ERD.  Draw the Logical schema for the ERD. Underline the primary keys. 

1) Given: - (p.s: This question was given to candidates during an interview for Tech position )

Customer Table

Order Table

Delivery_Coverage

SELECT Customer_Table.State, Order_Table.Item_ID, Sum(Quantity)

FROM Customer_Table, Order_Table

WHERE Customer_Table.Customer_ID = Order_Table.Customer_ID AND

Order_Table.Date < ‘2-28-1999’ AND

Customer_Table.State IN (SELECT State_Serviced from Delivery_Coverage WHERE Name = ‘ABC Trucking’)

GROUP BY State, Item_ID

ORDER BY State, Item_ID

INTO TABLE Ship_Table

What resulting table will the above SQL statement(one query) yield?

Ship_Table

MIS 343  Final ExamName: _____________________________________Student ID: ________________(In Blocks Letters)Good Luck!1) Write a SQL Statement to create the following table(5)EmployeeEMPNOENAMEJOBHIREDATESALDEPTNONOT NULLNUMBER(4) – Primary KeyVARCHAR2(10) – Not NullVARCHAR2(9)DATENUMBER(7,2)NUMBER(2) – Foreign Key coming from Department table1

• 1495674900-MIS343zg.docx

ComputerScienceExpert

(11)

Status NEW Posted 25 May 2017 01:05 AM My Price 9.00

-----------

Attachments

file 1495674905-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer