Fundamentals of Database System (LabQuiz)

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

Question text

Database Tables: 

employee (EmpId, ename, bdate, title, salary, DeptId) 

project (ProjId, pname, budget, DeptId) 

department (DeptId, dname, mgreno) 

workson (EmpId, ProjId, resp, hours)

Write an SQL query that returns the project number and name for projects with a budget less than 50,000.

a.

SELECT ProjId, pname FROM project WHERE budget > 50000

b.

SELECT ProjId, pname FROM project WHERE budget < 50000

c.

SELECT * FROM project WHERE budget < 50000

d.

SELECT *, pname FROM project WHERE budget < 50000

Question text

Database Tables: 

employee (EmpId, ename, bdate, title, salary, DeptId) 

project (ProjId, pname, budget, DeptId) 

department (DeptId, dname, mgreno) 

workson (EmpId, ProjId, resp, hours)

Write an SQL query that returns all works on records where hours worked is less than 10 and the responsibility is 'Manager'.

a.

SELECT ProjId, * FROM workson WHERE hours < 10 AND resp = 'Manager'

b.

SELECT * FROM workson WHERE hours < 10 AND resp = 'Manager'

c.

SELECT * FROM workson WHERE hours <= 10 AND resp = 'Manager'

d.

SELECT * FROM workson WHERE hours > 10 AND resp = 'Manager'

Question text

Database Tables: 

employee (EmpId, ename, bdate, title, salary, DeptId) 

project (ProjId, pname, budget, DeptId) 

department (DeptId, dname, mgreno) 

workson (EmpId, ProjId, resp, hours)

 Write an SQL query that returns the departments (all fields) ordered by ascending department name.

a.

SELECT dname,mgreno FROM department ORDER BY dname

b.

SELECT * FROM department ORDER BY dname ASC

c.

SELECT dname,mgreno FROM department ORDER BY dname DESC

d.

SELECT * FROM department ORDER BY dname DESC

Question text

Database Tables: 

employee (EmpId, ename, bdate, title, salary, DeptId) 

project (ProjId, pname, budget, DeptId) 

department (DeptId, dname, mgreno) 

workson (EmpId, ProjId, resp, hours)

Write an SQL query that returns the employee name, department name, and employee title.

a.

SELECT ename, dname, title FROM employee e, department d WHERE e.DeptId = d.DeptId 

b.

SELECT ename, dname, title FROM employee e, dept d WHERE e.DeptId = d.DeptId 

c.

SELECT ename, dname, title FROM employee, department 

d.

SELECT * FROM employee, department WHERE e.DeptId = d.DeptId 

Question text

Database Tables: 

employee (EmpId, ename, bdate, title, salary, DeptId) 

project (ProjId, pname, budget, DeptId) 

department (DeptId, dname, mgreno) 

workson (EmpId, ProjId, resp, hours)

Write an SQL query that returns all employee information.

a.

select * from employee 

b.

select EmpId, ename, title, DeptId from employee 

c.

select EmpId, ename, title, salary, DeptId from employee 

d.

select *, EmpId, ename, title, DeptId from employee 

Question text

Write an SQL query to show the second highest salary from a table.

a.

Select max(Salary) from Worker where Salary not in (Select Salary from Worker);

b.

Select max(Salary) from Worker where Salary not in (Select max(Salary) from Worker);

c.

Select max(Salary) from Worker;

d.

Select max(Salary) from Worker where Salary = (Select max(Salary) from Worker);

Question text

Write an SQL query to fetch the departments that have less than five people in it.

a.

SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID);

b.

SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 5;

c.

SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) > 5;

d.

SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT;Feedback

Question text

Write an SQL query to show the last record from a table.

a.

Select * from Worker where WORKER_ID = (SELECT WORKER_ID from Worker);

b.

Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID));

c.

Select * from Worker where WORKER_ID = (SELECT WORKER_ID);

d.

Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID) from Worker);

Question text

Write an SQL query to show only even rows from a table.

a.

SELECT * FROM Worker WHERE MOD (WORKER_ID, 1) = 0;

b.

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 2;

c.

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 1;

d.

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;

Question text

Write an SQL query to fetch the names of workers who earn the highest salary.

a.

SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY));

b.

SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);

c.

SELECT FIRST_NAME, SALARY from Worker WHERE SALARY(SELECT max(SALARY) from Worker);

d.

SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT SALARY from Worker);

Question text

Determine the normal form using the following sets of table.

Emp_Course(Emp_ID, Course_Title, Date_Completed)

a.

2nd Normal form

b.

3rd Normal Form

c.

None of the above

Question text

What is normal form of the following sets of tables?

Student(Student_ID, Major)
Major Advisor( Major, Advisor)

a.

3rd Normal form

b.

2nd normal form

c.

None of the above

Question text

Identify the foreign key for the department table below:

Employee1( Emp_ID, Name, Dept_Name, Salary)
Department (Dept_Name, Location, Fax)

a.

Dept_Name

b.

Location

c.

Fax

Question text

Merge the following two relation:

Employee1(Employee_ID, Name, Address, Phone)
Employee2(Employee_ID, Name, Address, Phone, Jobcode, No_Years)

a.

Employee(Employee_ID, Name, Address, Phone, Jobcode, No_Year)

b.

Employee(Emp_ID, name, address, phone, jobcode, no_years

c.

Employee(Emp_id, name, address,phone, jobcode, no_of_years)

Question text

It is a functional dependency between two nonkey attributes

a.

transitive dependency

b.

functional dependency

c.

None of the above

Question text

What type of relationship is this? Employee manages employees.

a.

Unary relationship

b.

Binary relationship

c.

Ternary relationship

Question text

Employee is assigned to a parking place is an example of unary relationship.

Select one:

True

False 

Question text

Product line contains many product. What is the cardinality?

a.

One-to-many

b.

One-to-one

c.

Many-to-one

Question text

What is the cardinality of this relation? Students registers for many courses.

a.

Many-to-many

b.

One-to-many

c.

One-to-Many

Question text

What is the cardinality using the following relation? A person is married to another person

a.

One-to-one

b.

One-to-many

c.

None of the above

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