SQL Server Basics
What is a database?
A. A mobile application that can access, change, and store data
B. A system capable of storing, organizing, and managing a large amount of data
C. Any server application responsible for reading and writing data
D. Any text file that can be opened using a computer.
B. A system capable of storing, organizing, and managing a large amount of data
The statement should retrieve ProductNumber and StandardCost column for products that are both of Size 42 and price less than 1000.
SELECT ProductNumber, ___ FROM Production.Product WHERE Size = ___ AND ___ < ___
A. 1000, StandardCost, '42', ListPrice
B. StandardCost, '42', ListPrice, 1000
C. 1000, ListPrice, '42', StandardCost
B. StandardCost, '42', ListPrice, 1000
What is the correct SQL statement to retrieve rows from a table, ordered by the LastName column in descending order?
A. SELECT * FROM Person.Person ORDER LastName DESC
B. SELECT * FROM Person.Person ORDER LastName
C. SELECT * FROM Person.Person ORDER BY LastName DESC
D. SELECT * FROM Person.Person ORDER BY LastName
C. SELECT * FROM Person.Person ORDER BY LastName DESC
Correct the SQL statement in order to retrieve the maximum ListPrice value from Product table.
SELECT SUM(ListPrice) FROM Product
SELECT MAX(ListPrice) FROM Product
What type of database stores data in a simple, unstructured format?
A. Flat file database
B. Relational database
C. Object-oriented database
A. Flat file database
We want to be able to store a whole number into the table column. We also want to be able to store empty data into that column.
To solve the problem, we need to make that column of type ___ and make it ___.
A. datetime, int
B. nullable, datetime
C. int, nullable
D. normalized, float
C. int, nullable
Retrieve only the first 3 records from the table Person.
SELECT * FROM Person
LIMIT 3;
Retrieve the full name from table Production.Product. The new FullName column should contain data from FirstName and LastName columns separated by one space character.
SELECT ___ AS ___
FROM Person.Person
A. FirstName + ' ' + LastName, FullName
B. FullName, FirstName
C. FirstName, LastName
D. FirstName + LastName, FullName,
A. FirstName + ' ' + LastName, FullName
What type of database is best suited for large, complex data structures?
A. Hierarchical database
B. Relational database
C. Object-oriented database
D. Flat file database
B. Relational database
___ is a language to query data from the table. ___ is a language to change data in a table. ___ is a language to create, modify or delete a table.
A. DQL, DML, DDL
B. DML, DDL, DQL
C. DDL, DQL, DML
A. DQL, DML, DDL
Change the SQL statement in order to retrieve LastName and FirstName columns from Person table. Data should be ordered by LastName ascending, and then by FirstName descending.
SELECT * FROM Person
SELECT * FROM Person
ORDER BY LastName ASC, FirstName DESC;
Retrieve year, month and day of month from BirthDate of HumanResources.Employee table.
SELECT BirthDate, ___(BirthDate), ___(BirthDate), ___(BirthDate) FROM HumanResources.Employee
A. yyyy, day, month
B. year, month, day
C. day, dayofmonth, year
D. dayofmonth, yyyy, day
B. year, month, day
Choose the systems that are classified as relational databases.
A. Oracle
B. PostgreSQL
C. Microsoft Windows
D. Linux
E. Microsoft SQL Server
A Oracle
B. PostgreSQL
E. Microsoft SQL Server
Change the following statement in order to display data Hello World! without error, in a column named Greeting.
SELECT Hello World!
SELECT 'Hello World!' AS Greeting;
Correct the SQL statement in order to work correctly. Statement should group data from Employee table by JobTitle and OrganizationLevel.
SELECT JobTitle, OrganizationLevel FROM Employee
SELECT JobTitle, OrganizationLevel FROM Employee
GROUP BY JobTitle, OrganizationLevel;
Retrieve count of records from Production.Product table, grouped by column Size.
Only include products with a weight less than 35, and groups that have more than 15 records.
SELECT ___, ___
FROM [Production].[Product]
WHERE ___ < ___
GROUP BY Size
HAVING COUNT(*) > ___
A. Size, COUNT(*), Weight, 35, 15
B. Size, SUM(*), Weight, 15, 35
C. COUNT(*), Size, 15, Weight, 35
A. Size, COUNT(*), Weight, 35, 15
Identify the format that does NOT correspond to a flat-file database.
A. XML
B. CSV
C. PPT
D. JSON
C. PPT
Retrieve all the rows with all columns from Employee table with JobTitle that is different than 'Sales Representative'.
SELECT JobTitle FROM Employee
WHERE JobTitle != 'Sales Representative';
Retrieve the data from HumanResources.Employee. Data should be grouped by OrganizationLevel, and for each organization level there should be a number of records from the table displayed in the column LvlCount.
SELECT * FROM HumanResources.Employee
SELECT OrganizationLevel, COUNT(OrganizationLevel) AS LvlCount
FROM Employee
What is the correct expression to round the ListPrice column to two decimal places (i.e., to cents) for each product?
A. SELECT Name, ROUND(ListPrice, 1) FROM Production.Product
B. SELECT Name, ROUND(ListPrice, C) FROM Production.Product
C. SELECT Name, ROUND(ListPrice, 2) FROM Production.Product
D. SELECT Name, ROUND(ListPrice, 'cents') FROM Production.Product
C. SELECT Name, ROUND(ListPrice, 2) FROM Production.Product