SQL Final FPT

Database Concepts & ER Models

A. Structured Query Language

Which of the following is described graphically using an E-R diagram?

C. Entities and relationships between entities

SQL query and modification commands make up a(n)

B. DML

A/an____ property of the entity and it quantifies, qualifies, classifies or specifies the entity. is a piece of information that in some way describes an entity. It is a

D. Attribute

What is the purpose of a Unique Constraint?

D. To identify one unique instance of an entity, by using one or more attributes and/or relationships

Which of the following entities most likely contains invalid attributes?

C. Entity : Car. Attributes : Owner Occupation, Owner Salary, Speed

In a 1 : N relationship, the foreign key is placed in :

C. the child table

You are building a new database for a company with 10 departments. Each department contains multiple employees. In addition, each employee might work for several departments. How should you logically model the relationship between the department entity?

B. Create a new entry, create a one-to-many relationship from the employee to the new entry, and create a one-to-many relationship from the department entry to the new entry.

In which of the following can many entity instances of one type be related to many entity instances of another type?

C. Many-to-Many Relationship

In a 1 : 1 relationship, the foreign key is placed in

A. either table without specifying parent and child tables

What is MS SQLServer?

D. Microsoft SQL Server is a database platform for online Transaction Processing (OLTP), data warehousing, e-commerce application. It is also a business intelligence platform for data intergration, analysis, and reporting solutions.

What is components of MS SQL server?

A. Database Engine, Intergration Services, Reporting Services, Analysis Services.

What is database engine?

C. Database Engine is the core service for storing, processing, and securing data.

What is integration services (SSIS)?

B. Integration Services (SISS) is a platform for building high performance data intergration solutions, including extraction, transformation, and load packages for data warehousing.

What data type is "Distance from home to school "should be?

A. Decimal

Which of the following is not an operator in categories of SQL Comparison Operators?

C. Addition (+)

What is the storage size of smallmoney data type?

C. 4 bytes

DECLARE @message CHAR(10), @error_id INT

SET @message = 'Error: '

SET @error_id = 1

IF @error_id=1

SET @message = @message+' this field is not define'

PRINT @message

When execute the code, which message is printed:

A. Error:

To sum integers from 1 to 10 by using a WHILE loop. Consider the following code snippet:

DECLARE @i INT, @sum INT

SET @i=1

SET @sum=0

WHILE @i

D. The loop never stops

The NULL SQL keyword is used to ...

D. represent a missing or unknown value.

What is the purpose of the SQL AS clause?

C. The AS SQL clause is used change the name of a column in the result set or to assign a name to a derived column.

Which of the following SQL statements is used to delete both the structure and data of the named table STUDENT ?

A. DROP TABLE STUDENT

Which of the following SQL statements is used create database FASM in SQL Server?

C. CREATE DATABASE FASM;

Which one of the following deletes all the entries but keeps the structure of the relation.

D. Delete from instructor;

The SQL ALTER statement can be used to:

A. change the table structure

SQL data definition commands make up a(n) ________________

A. DDL

Identify the correct syntax for IDENTITY property?

A. Column-name Data type IDENTITY(SEED, INCREMENT)

A primary key constraint cannot be deleted if it is being referenced by a foreign key constraint in another table; the foreign key constraint must be deleted first?

A. TRUE

A foreign key is:

A. a column containing the primary key of another table

The difference between the DELETE and TRUNCATE SQL clauses is:

C. The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table.

LOCATIONS(subject_code, department_name, location_id, city);

Which code snippet will alter the table LOCATIONS and change the datatype of the column CITY to varchar(30)?

C. ALTER TABLE locations ALTER COLUMN city varchar(30)

Which of the following is not true about complex views?

B. They contain no functions or grouping.

Which of the following code will delete a view named all_marks_english?

B. drop view all_marks_english;

What is an index ?

B. An index is a database table attribute, which speeds-up data search within a table.

Which is the purpose of index in SQL server?

D. All of the above

Which one of the following is not true for a view?

B. They contain no functions or grouping

Refer below query which leads to create a view named vwEmployee?

CREATE vwEmployee VIEW

AS

SELECT nothing

FROM dbo.Employee

WHERE ID < 100

Now, tell the problem in query?

B. View name must be after keyword view and 'nothing' is not keyword, so should be replace with *.

Given SQL statement:

UPDATE Products

SET UnitPrice = UnitPrice + (UnitPrice * .10)

WHERE UnitPrice BETWEEN 60 AND 70

Which of the following is true ?

B. 10% price increase (UnitPrice) in the Products table, for products priced between 60 and 70.

In the UPDATE statement, if we do not use the WHERE clause, then:

D. All of the records will be updated

The SQL WHERE clause

B. limits the row data are returned.

The SQL keyword(s) ________ is used with wildcards.

A. LIKE only

Which of the following is true about the COUNT function?

D. All are true.

Given the following table and query:

Employee (employee_id, name, salary)

(1001, 'Annie', 6000),

(1009, 'Ross', 4500),

(1018, 'Zeith', 7000)

and

SELECT * FROM employee WHERE employee_id>1009;

D. 1018

The following table

BOOK_INFORMATION(book_id, book_title, price)

Which SQL statement allows you to insert the following price of data into BOOK_INFORMATION ?

C. INSERT INTO BOOK_INFORMATION VALUES (20, 'FRESHER ACADEMY SQL TUTORIAL', 15);

: The following table:

BOOK_INFORMATION(book_id, book_title, price)

Which SQL statement will you use to change the price for the BOOK titled 'ELEMENTARY SCHOOL GUIDE' to 20?

A. UPDATE BOOK_INFORMATION SET price = 20 WHERE book_title = 'ELEMENTARY SCHOOL GUIDE';

Which SQL keyword is used to remove duplicate rows in the result of an SQL query SELECT ?

D. DISTINCT

Consider the following schema:

products (ProductName, UnitPrice, UnitsInStock)

Which of the following query statements is syntax error?

SELECT ProductName FROM products WHERE (UnitPrice < 10) , (UnitsInStock > 5);

Given below data in the following table:

Example_Table (Number, Name)

(1, 'One'),

(2, 'Two'),

(3, 'Three'),

(4, 'Four'),

(5, 'Five'),

(1, 'One'),

(NULL, NULL)

and the query:

SELECT COUNT(*) FROM Example_Table as COUNT

SELECT COUNT_BIG(*) FROM Example_Table as COUNT_BIG

Result of the above two queries is:

B. 7,7

Which one is correct syntax for applying UNION operator:

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2;

Which of the following query is correct to select the highest salary of the employees in each Department?

C. SELECT e.dept_id, MAX(e.salary) FROM Employee e GROUP BY e.dept_id;

SELECT i.name FROM instructor i

WHERE i.dept_name = 'Physics'

ORDER BY i.name;

By default, the order by clause lists items in ______ order.

D. Ascending

What is the difference between the WHERE and HAVING SQL clauses

A. The WHERE SQL clause condition(s) is applied to all rows in the result set before the HAVING clause is applied (if present). The HAVING clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group.

Given the following table:

BOOK_INFORMATION(book_id, book_title, price)

Which of the following SQL statements would find all books whose title starts with 'A'?

SELECT book_id, book_title FROM BOOK_INFORMATION WHERE book_title LIKE '%A';

Given the following table:

SALES (store_id, sales_date, sales_amount)

Which of the following SQL statements would list all stores that total sales amount is over 5000?

SELECT store_id, SUM(sales_amount)

FROM SALES

GROUP BY store_id

HAVING SUM(sales_amount) > 5000;

What does the UNION operator do?

C. The UNION operator combines the results of two or more queries into one result that includes all the rows from the queries in the union.

Which of the following query would show the total number of lines in the 'emp' table?

C. SELECT count(*) FROM emp

Consider the following table:

Product(productId, name, cost)

Which of the following query would display the lowest of cost in the Product table?

B. Select min(cost) from Product;

Consider the following table and query:

Bill(bill_id, export_date, customer_id)

and

SELECT COUNT(bill_id)

FROM Bill

GROUP BY export_date;

Which of the following is true?

C. List out number of bills by the date

We are running below query on 4-Apr-2014 at 9:25 am

SELECT CONCAT('Date & Time:', GETDATE()+7-6)

It will return

D. Date & Time: Apr 5 2014 9:25AM

What is purpose of CAST function in SQL Server?

A. It converts or cast an expression of 1 data type to another

Give 2 tables have schema as bellow:

STUDENT(studentId, firstName, lastName, departmentId)

RESULT(studentId, subjectId, score).

To select students in the TH department with the highest average of score, which statement should you use?

SELECT kq.studentId, sv.firstName, sv.lastName, AVG(kq.score)

FROM dbo.Result kq INNER JOIN dbo.Student sv ON kq.studentId=sv.studentId

WHERE sv.departmentId='TH'

GROUP BY kq.studentId, sv.firstName, sv.lastName HAVING AVG(kq.score) IN

(SELECT TOP 1 AVG(kq.score)

FROM dbo.Result kq INNER JOIN dbo.Student sv ON kq.studentId=sv.studentId

WHERE sv.departmentId='TH'

GROUP BY kq.studentId ORDER BY AVG(kq.score) DESC);

In SQL, the ABS function used for?

A. To return the absolute, positive value of a numeric expression

The AVG SQL function returns the ...

C. average in the values in agroup

A data manipulation command the combines the records from one or more tables is called

C. JOIN

Give the database schema the following tables:

Student (studentId, firstName, lastName, gender, birthday, phone, address, classId)

Class (classId, className)

Which code snippet will list the id and names of class with more than 15 students?

SELECT c.classId, c.className, COUNT(*) AS amount FROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classId

GROUP BY c.classId, c.className HAVING COUNT(*) > 15;

Give the database schema the following tables:

Student (studentId, firstName, lastName, gender, birthday, phone, address, classId)

Class (classId, className)

Which code snippet will display records with the following three columns: classId, className and amount (amount of students)?Class

SELECT c.classId, c.className, COUNT(*) AS amount FROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classId

GROUP BY c.classId, c.className;

For the following tables (schema is 'asql'):

Student(studentId, studentName, address)

('S01', 'Nguyen Van An', 'Hanoi'),

('S02', 'Pham Tuan Anh', 'Hanoi'),

('S03', 'Nguyen Minh Quan', 'Danang')

Result(studentId, subjectId, score)

('S01', 'J01', 8),

('S01', 'S01', 7),

('S02', 'J01', 3)

Subject(subjectId, subjectName)

('S01', 'SQL Basics'),

('J01', 'Programming Java'),

('N01', 'Programming C#')

and a query:

SELECT s.studentName, r.subjectId, r.score FROM asql.Student s JOIN asql.Result r ON s.studentId =r.studentId;

What is the result of the query?

Nguyen Van An J01 8

Nguyen Van An S01 7

Pham Tuan Anh J01 3

For the following tables:

Customers (CustomerID, CustomerName, PhoneNumber, Address)

Orders (OrderID, OrderDate, CustomerID)

To return the CustomerName and OrderDate that the result must include the customers who have not placed any orders. Which Transact-SQL query should you use?

SELECT CustomerName, OrderDate

FROM Customers LEFT OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

Which one is NOT type of Outer Join ?

D. BOTH JOIN

Product (Product_No, Name)

('S101', 'A'),

('S102', 'B'),

('S103', 'C'),

'S104', 'D')

Sales_Details (Sales_No, Product_No, Quantity)

('U001', 'S101', 5),

('U002', 'S104', 2),

('U002', 'S101', 10),

('U003', 'S103', 5),

('U003', 'S104', 8)

and

SELECT X.Produduct_No FROM Product X LEFT OUTER JOIN Sales_Details Y ON X.Product_No=Y.Product_No;

A. S101, S101, S102, S103, S104, S104

The following SQL is which type of join :

SELECT CUSTOMER_T.CUSTOMER_ID, ORDER_T.CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T, ORDER_T ;

B. CROSS JOIN

We refer to a join as a self-join when?

D. we are joining table to itself

SELECT X.PK

FROM TableX X

LEFT JOIN TABLEY Y

ON X.PK = Y.PK

WHERE Y.PK IS NULL

What is result of the query?

4 5 10

SELECT Y.PK

FROM TableX X

RIGHT JOIN TABLEY Y

ON X.PK = Y.PK

WHERE X.PK IS NULL

What is result of the query?

8 9 11

SELECT Y.PK

FROM TableX X

FULL OUTER JOIN TABLEY Y

ON X.PK = Y.PK

WHERE X.PK IS NULL OR Y.PK IS NULL

What is result of the query?

NULL NULL 8 9 NULL 11

How many tables can be included with a join ?

D. All of the mentioned options