linkedin-skill-assessments-quizzes

Transact-SQL (T-SQL)

Q1. Which answer is NOT a type of table index?

Q2. The keywords AND, IN, LIKE, and between all belong to a category called what?

Q3. What is the result of this series of statements?

BEGIN TRY
SELECT 'Foo' AS Result;
END TRY
BEGIN CATCH
SELECT 'Bar' AS Result;
END CATCH

Q4. Given these two tables, which query generates a listing showing student names and the department office location where you could reach each student?

Q5. What is an example of a DDL command in SQL?

Q6. Given the Games table pictured, which query generates the results shown?

  SELECT GameType, MaxPlayers, count(*) AS NumberOfGames
  FROM Games
  GROUP BY MaxPlayers, GameType
  ORDER BY MaxPlayers, GameType;

  SELECT GameType, MaxPlayers, count(*) AS NumberOfGames
  FROM Games
  GROUP BY GameType, MaxPlayers
  ORDER BY GameType;

  SELECT GameType, count(Players) AS MaxPlayers, NumberOfGames
  FROM Games
  GROUP BY GameType, MaxPlayers
  ORDER BY GameType;

  SELECT GameType, MaxPlayers, count(*) AS NumberOfGames
  FROM Games
  GROUP BY GameType
  ORDER BY MaxPlayers;

Q7. Which answer is a possible result of the sequence of commands below?

  DECLARE @UniqueID uniqueidentifier = NEWID();
  SELECT @UniqueID AS Result;

Q8. You need to find all students that are not on the “Chemistry Cats” team. Which query does NOT work for this task?

```SELECT * FROM Students WHERE team NOT ‘Chemistry Cats’;


- [ ] :

```SELECT * FROM Students
   WHERE team <> 'Chemistry Cats';

```SELECT * FROM Students WHERE team != ‘Chemistry Cats’;


- [ ] :

```SELECT * FROM Students
   WHERE NOT team = 'Chemistry Cats';

Q9. You need to write a query that returns all Employees that have a LastName starting with the letter A. Which WHERE clause should you use to fill in the blank in this query?

Q10. Which query shows the first name, department, and team of all students with the two lowest points?

the picture of table is important here and there it can be seen that there are only two value with min points. Secondly, the previous answer was wrong because order by DESC will put highest points into the beginning of result list and TOP(2) will take first two highest points, and we need the lowest points.

Q11. What is the result of this statement?

SELECT FLOOR(-1234.321)

Q12. Which is the best approach to update the last name of the student Donette Figgins to Smith

Q13. Which of these data types is an approximate numeric?

Q14. You need to remove all data from a table name Products. Which query fully logs the removal of each record?

Q15. What is the result of this query?

SELECT 1 / 2 AS Result;

Q16. which data type will most efficiently store a person’s age in years?

Q17. What is the result of this query?

SELECT 'abc\
def' AS Result;

Q18. To select a random student from the table, which statement could you use?

https://www.petefreitag.com/item/466.cfm

Q19. What result is returned after executing the following commands?

DECLARE @MyVariable int;
SET @MyVariable = 1;
GO
SELECT @MyVariable;

Q20. Which statement creates a new database schema named Sales and establish Sharon as the owner?

Reference link

Q21. The result of a CROSS JOIN between a table with 4 rows, and one with 5 rows, will give with _ rows.

Q22. You need to write a query that returns all products that have a SerialNumber ending with “10_3”. Which WHERE clause should you use to fill in the blank in this query?

SELECT ProductID, ProductName, SerialNumber
FROM Products______ ;

The underscore will match any single character, therefore you need to wrap the literal _ with square brackets, otherwise, it may return a serial number ending with ‘1013’, ‘10A3’, etc.

Reference link

Q23. When no join type between multiple tables in a query’s FROM clause is specified, what type of join is assumed?

Q24. How many bytes of storage does the int data type consume?

Q25. What does a RIGHT JOIN ensure?

Q26. You execute the following three queries. What is the result?

Create table students(id int identity(1000,1), firstname varchar(20),
lastname varchar(30));
insert into students(firstname,lastname)values('mark','twain');
select * from students;

Q27. Given a table with the following structure, which query returns all student names with the highest grade?

CREATE TABLE Students (
StudentName varchar(50),
Grade int );

top(1) with ties will take the highest grade and all other students with the same grade (because they are order by grade) and matches the highest grade.

Q28. What role does “inventory” play?

select bookid, boooktitle, bookauthor,quantityonhand from inventory.books;

select * from dbo.books here dbo is a schema and the inventory is also schema. If we’d like to specify a database we should use db_name.schema_name.table_name

Q29. What is the result of an INNER JOIN between table1 and table2?

Q30. To remove all of the content from the Students table but keep the schema, which statement should you use?

Q31. Review the CREATE TABLE statement below. Which option, when placed in the blank space, ensures that the BookISBN column will not contain any duplicate values?

CREATE TABLE Books (
    BookID int PRIMARY KEY,
    BookISBN char(13) NOT NULL _____,
    BookTitle nvarchar(100) NOT NULL
);

Q32. Given a table with the following structure, which query will not return the lowest grade earned by any student?

CREATE TABLE Students (
    StudentName varchar(50),
    Grade int
);

SELECT StudentName
FROM Students
WHERE Grade = (SELECT MIN(Grade) FROM Student);

SELECT TOP(1) Grade
FROM Students
ORDER BY Grade;

SELECT MIN(Grade)
FROM Students

SELECT MIN(Grade)
FROM Students
ORDER BY Grade;

Explanation: Column Students.Grade is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT MIN(Grade)
FROM Students
GROUP BY Grade;

Explanation: Grouping will return a list of all grades grouped by grade. The prompt wants just one returned row.

Q33. Refer to the figure below.

T-SQL-Q33

Q34. You would like to have a record added to a TableB every time a record is modified in TableA. What technique should you look at implementing?

Q35. What is the problem with this code?

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter > 0
BEGIN
	SET @Counter = @Counter +1;
END;

Q36. Which is the right query to change the name of the Philosophy Pandas team to the Philosophy Parrots?

T-SQL-Q36

Q37. What is the result of this query?

SELECT 123+'123' AS Result;

Q38. To combine the results of two or more SELECT statements, removing duplicates, which keyword can you use?

Q39. You run this series of statements. What is the final result?

CREATE TABLE MyTable (MyValue int);
INSERT INTO MyTable VALUES (1);
WHILE (SELECT MyValue FROM MyTable) < 5
BEGIN
	UPDATE My Table SET MyValue = MyValue + 1;
END;
SELECT MyValue AS Result FROM MyTable;

Q40. Is there an error with this query? If so, which statement best describes the problem?

SELECT OrderID, SUM(LineTotal) AS SubTotal
FROM Sales
WHERE SUM(LineTotal) > 1000
GROUP BY OrderID
ORDER BY OrderID;

CREATE TABLE Books (
	BookID int PRIMARY KEY,
	BookTitle nvarchar(100) NOT NULL,
	PublisherID int NOT NULL
);
CREATE TABLE Publishers (
	PublisherID int PRIMARY KEY,
	PublisherName nvarchar(50)
);

ALTER TABLE Books
ADD CONSTRAINT FK Books_PublisherID
FOREIGN KEY (PublisherID)
REFERENCES Publishers (PublisherID) ON UPDATE SET NULL

ALTER TABLE Books
ADD CONSTRAINT FK Books_PublisherID
FOREIGN KEY (PublisherID)
REFERENCES Publishers (PublisherID) ON DELETE CASCADE

ALTER TABLE Books
ADD CONSTRAINT FK_Books_PublisherID
FOREIGN KEY (PublisherID)
REFERENCES Publishers (PublisherID)

ALTER TABLE Publishers
ADD CONSTRAINT FK_Publishers_PublisherID
FOREIGN KEY (PublisherID)
REFERENCES Books (PublisherID) CASCADE DELETE

Q42. Your database currently has a table called Inventory in the Warehouse schema. You need to move the table to the Products schema. Which query accomplishes this goal?

Q43. Which option—when placed in the blank space—establishes the PersonlD column as the primary key for the table with a nonclustered index?

CREATE TABLE People (
	PersonID int NOT NULL,
	PersonName nvarchar(50),
_______
);

Q44. Which statement could you use to select a random student from this table?

T-SQL-Q36

Q45. You need to create a simple database backup in the server’s Z:\Backups directory. Which query should you use?

Q46. Suppose you want to have the name of a transaction called myTransaction recorded in the transaction log. Which statement represents the best way to accomplish this?

Q47. Though not currently a requirement, what will a future release of SQL Server require of all SQL statements?Though not currently a requirement, what will a future release of SQL Server require of all SQL statements?

Q48. Which is the best approach to update the last name and email address of a student with ID 56295?

Q49. What is the result of this query?

SELECT 123+'abc' AS Result;

Conversion failed when converting the varchar value ‘abc’ to data type int.

Q50.What output will the following SQL sequence produce? Assume that the tables have been created and all the columns exist.

INSERT INTO Account (acct,bal) VALUES ('12345', 100);
UPDATE Account SET bal=bal+100;
BEGIN;
UPDATE Account SET bal=bal+100.
ROLLBACK;
SELECT bal FROM Account WHERE acct='12345';
);

Reference link

Q51. The Marketing department wants to send an email to each member of the Humanities department. Based on the table below, which query gives them the first name and email address of each member of that department?

T-SQL-Q36

Q52. Which statement deletes a table named Inventory from the Products database?

  DROP TABLE Products.Inventory;

  USE Products;
  DROP TABLE Inventory;

  USE Products;
  DELETE Inventory;

  USE Products.Inventory;
  DROP TABLE Inventory;

This statement first switches to the Products database using the USE command and then drops the Inventory table using the DROP TABLE command.

Q53. In a SELECT statement, which clause should always be used with the TOP clause in order to predictably indicate which rows are affected by TOP?

Q54. Which data type should you choose when you nedd to store dates and times that include time zone information?

Q55. What is the result of this query?

SELECT 123+'123' AS Result;

Q56. What is the result of these three commands?

CREATE TABLE MyNumbers (
    MyDecimalColumn decimal(5,2) NOT NULL
);

INSERT INTO MyNumbers VALUES (123), (45);

SELECT * FROM MyNumbers;

Q57. Given the table below , which query shows How many students are in each department ?

T-SQL-Q55

Q58. What is an example of a DDL command in SQL ?

Q59. Which statement deletes a table named Inventory from the Products database?

  DROP TABLE Products.Inventory;

  USE Products;
  DROP TABLE Inventory;

  USE Products;
  DELETE Inventory;

  USE Products.Inventory;
  DROP TABLE Inventory;
This site is open source. Improve this page.