Fundamentals of Database System (LabQuiz)

Fundamentals of Database System
(PreLabQuiz)
(PreLabExam)
(MidLabQuiz)
(MidLabExam)
(FinLabActivity)
(FinLabQuiz)

Question text

Given the following data definition, select all city names in descending order:

TABLE cities

id INTEGER NOT NULL PRIMARY KEY 

name VARCHAR(30) NOT NULL

a.

select name from cities order by name desc

b.

select * from cities order by name desc

c.

select * from cities order by name asc

d.

select name from cities order by name 

Question text

Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000.

a.

SELECT * FROM EmployeeSalary WHERE Salary BETWEEN 9000 AND 15000;

b.

SELECT * FROM EmployeeSalary WHERE Salary 9000 AND 15000;

c.

SELECT EmpId, Salary FROM EmployeeSalary WHERE Salary BETWEEN 15000 AND 9000;

d.

SELECT EmpId, Salary FROM EmployeeSalary WHERE Salary BETWEEN 9000 AND 15000;

Question text

Write an SQL query to fetch all the employees who either live in California or work under a manager with ManagerId – 321.

a.

SELECT EmpId, City, ManagerId FROM EmployeeDetails WHERE City='California' and ManagerId='321';

b.

SELECT* FROM EmployeeDetails WHERE City='California' OR ManagerId='321';

c.

SELECT EmpId, City, ManagerId FROM EmployeeDetails WHERE City='California' OR ManagerId='321';

d.

SELECT * FROM EmployeeDetails WHERE City='California' and ManagerId='321';

Question text

Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ending with any sequence of characters.

a.

SELECT FullName FROM EmployeeDetails WHERE FullName LIKE ‘__hn%’;

b.

SELECT FullName FROM EmployeeDetails WHERE FullName LIKE ‘_hn%’;

c.

SELECT FullName FROM EmployeeDetails WHERE FullName LIKE ‘__hn’;

d.

SELECT FullName FROM EmployeeDetails WHERE FullName LIKE ‘__hn__’;

Question text

Fetch all the employees who are not working on any project.

a.

SELECT EmpId FROM EmployeeSalary WHERE Project IS NULL;

b.

SELECT EmpId FROM EmployeeSalary WHERE Project ="0";

c.

SELECT EmpId FROM EmployeeSalary WHERE Project NULL;

d.

SELECT EmpId FROM EmployeeSalary WHERE Project;

Question text

salesman_id |             name    |                city   |              commission

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

        5001 |           James Hoog |       New York |                0.15

        5002 |               Nail Knite |            Paris    |                0.13

        5005 |                 Pit Alex   |        London   |                0.11

        5006 |               Mc Lyon    |           Paris    |                0.14

        5007 |             Paul Adam  |        Rome     |                0.13

        5003 |            Lauson Hen |        San Jose |                0.12 

You need to create a table for a salesmen table, which statement creates the table above?

a.

Create table salesmen( salesman_id int not null primary key, name varchar(30), city varchar(30), commission decimal(4,2));

b.

Create table salesmen(salesman_id int not null, primary key, name varchar(30), city varchar(30), commission decimal(4,2));

c.

Create table salesmen( salesman_id int not null primary key, name varchar(30), city varchar(30), commission number(4,2));

Question text

salesman_id |           name    |                  city   |          commission

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

        5001 |       James Hoog |            New York |              0.15

        5002 |           Nail Knite |                 Paris    |              0.13

        5005 |            Pit Alex   |              London   |              0.11

        5006 |         Mc Lyon    |                  Paris    |              0.14

        5007 |       Paul Adam  |                Rome     |             0.13

        5003 |      Lauson Hen |                San Jose |             0.12

Which SQL statement below  displays all the information of all salesmen?

a.

Select * from salesmen;

b.

Select all from salesmen

c.

Select salesman_id, name, city, commission from salesmen;

Question text

Which SQL statement will display the names and city of the salesman, who belongs to the city of Paris?

a.

Select name, city from salesmen where city= Paris ;

b.

Select name, city from salesmen where city='Paris'; 

c.

Select name, city from salesmen where city is equal to Paris;

Question text

YEAR                    SUBJECT  CATEGORY                       WINNER                                             COUNTRY                               

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

1970                       Physics Scientist                              Hannes Alfven                                      Sweden                 

1970                      Physics Scientist                                 Louis Neel                                           France                 

1970                      Chemistry Scientist                          Luis Federico Leloir                               France                 

1970                      Physiology Scientist                         Ulf von Euler                                         Sweden                 

1970                      Physiology Scientist                         Bernard Katz                                        Germany                

1970                      Literature Linguist                       Aleksandr Solzhenitsyn                             Russia                 

1970                      Economics Economist                      Paul Samuelson                                     USA                    

1970                       Physiology Scientist                        Julius Axelrod                                        USA                    

1971                       Physics Scientist                              Dennis Gabor                                     Hungary                

1971                       Chemistry Scientist                         Gerhard Herzberg                              Germany                

1971                       Peace  Chancellor                            Willy Brandt                                       Germany                

1971                       Literature Linguist                            Pablo Neruda                                     Chile                  

1971                       Economics Economist                      Simon Kuznets                                   Russia                 

1978                       Peace President                                Anwar al-Sadat                                  Egypt            

Using the nobel_win table above, give the name of the 'Physics Scientist' winner since 1970. 

Which of the following SQL statement produced the desired result?      

a.

Select year, subjectcategory, winner from nobel_win where subjectcategory= 'Physics Scientist' and year = 1970 ;   

b.

Select year, subjectcategory from nobel_win;

c.

Select year, subjectcategory from nobel_win where year= 1970;

Question text

Using the nobel_win table above, you need to write all the details of the Chemistry winner(year,subjectcategory, winner, country) from the year 1970. 

Which of the SQL statement would produce the required result?  

a.

Select * from nobel_win where subjectcategory='Chemistry Scientist' and year= 1970;

b.

Select year, subjectcategory, winner from nobel_win; 

c.

Select year, subjectcategory from nobel_win where subjectcategory= 'Chemistry Scientist';

Question text

Using the above nobel_win table, you need to generate a report that will show all the Physiology winners in the year 1970.

a.

Select year, subjectcategory, winner from nobel_win where subjectcategory = 'Physiology Scientist'  and year= 1970;

b.

Select year, subjectcategory, winner from nobel_win where subjectcategory = 'Physiology Scientist'  or year = 1970;

c.

None of the above

Question text

Using the salesmen table above, you need to generate a report that shows the name and commission from the table salesmen. 

Which SQL statement would produce the required result?

a.

Select name, commission from salesmen;

b.

Select * from salesmen;

c.

Select name and commission from salesmen;

Question text

You need to generate a report that will show all the salesmen's commissions with less than or equal to 15%. 

Which SQL statement produced the desired result?

a.

Select name, commission from salesmen where commission<0.15;

b.

Select name, commission from salesmen where commission<=0.15;

c.

Select name, commission from salesmen;

Question text

Which SQL statement displays the city of the salesmen that starts with the letter P?

a.

Select name, city from salesmen where city like 'P%';

b.

Select name, city from salesmen where city='Paris';

c.

Select name, city from salesmen;

Question text

Which SQL statement will display the name of the winner from Physiology Scientist?

a.

Select subjectcategory, winner from nobel_win where subjectcategory='Physiology Scientist';

b.

Select * from nobel_win where subjectcategory=' Physiology Scientist' ;

c.

Select * from nobel_win where subjectcategory like 'Physiology Scientist';

Question text

It is the process of producing a set of tables with proper attributes

a.

Normalized table

b.

Normal form

c.

Normalization

Question text

Based on the given table above, what is the primary key for the user table?

a.

userid, companyid

b.

companyid

c.

userid

Question text

Using the table below, what is the UNF of the following table?

a.

userID, name, company_id, company, company_address, url1,url2

b.

userid, name, company, company_address, url1,url2

c.

None of the above

Question text

CompanyTable( companyid, company, company_address)

What is the primary key?

a.

companyid

b.

company_id

c.

company id

Question text

UserTable(userid, name)

Using the above table, what is the primary key of the table named UserTable?

a.

userid

b.

user_id

c.

user id

Question text

In the given relationship, the doctor attends to the patient. What is the relationship between doctor and patients?

a.

attends

b.

treats

c.

admits

Question text

Entity CUSTOMER can be characterized by Id, Name, and Address. What is  sub-attributes  of the customer address?

a.

number, street

b.

street

c.

number, street, city, and state

Question text

An Engineer builds a house with few workers and raw materials participating in this building at the same time. What are the entities involved in this relation?

a.

All of the above

b.

Engineer, few workers and raw materials

c.

Engineer, house, few workers and raw materials

Question textGiven the following relationship, the patient was assigned to a room. What is the relationship between patient and room

a.

place

b.

assigned

c.

attends

Question text

An Engineer builds a house with few workers. Based on the given statement, what is the relationship between engineer and a few workers?

a.

builds

b.

makes

c.

creates