- nonclustered
- unique
- heap
- hash
- joining operations
- linking operations
- criteria operations
- logical operations
BEGIN TRY
SELECT 'Foo' AS Result;
END TRY
BEGIN CATCH
SELECT 'Bar' AS Result;
END CATCH
- Foo
- FooBar
- Foo Bar
- Bar
Q4. Given these two tables, which query generates a listing showing student names and the department office location where you could reach each student?
- SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students, Departments;
- SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students JOIN Departments ON Students.department = Departments.department;
- SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students JOIN Departments;
- SELECT Students.first_name, Students.last_name, Departments.office_location FROM Students ON Students.department = Departments.department;
- TRUNCATE TABLE
- DELETE
- MERGE
- DROP
- 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;
DECLARE @UniqueID uniqueidentifier = NEWID();
SELECT @UniqueID AS Result;
- 1
- bb261196-66a5-43af-815d-123fc593cf3a
- z350mpj1-62lx-40ww-9ho0-4u1875rt2mx4
- 0x2400001155F04846674AD4590F832C0
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?
- WHERE LastName = A*
- WHERE LastName = LIKE '%A%'
- WHERE LastName LIKE 'A%'
- WHERE LastName IN ('A*')
Q10. Which query shows the first name, department, and team of all students with the two lowest points?
- SELECT LIMIT(2) first_name, department, team FROM Students ORDER BY points ASC;
- SELECT TOP(2) first_name, deprtment, team FROM Students ORDER BY points DESC;
- SELECT TOP(2) WITH TIES first_name, department, team FROM Students ORDER BY points;
- SELECT BOTTOM(2) first_name, department, team FROM Students ORDER BY points ASC;
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.
SELECT FLOOR(-1234.321)
- -1234.3
- -1234
- -1235
- 1234.321
- UPDATE Students SET last_name = 'Smith' WHERE email = 'dfiggins@rouxacademy.com';
- UPDATE Students SET last_name = 'Figgins' WHERE email = 'dfiggins@rouxacademy.com';
- UPDATE Students SET last_name = 'Figgins' WHERE last_name = 'Smith' AND first-name = 'Donette';
- UPDATE Students SET last_name = 'Smith' WHERE last_name = 'Figgins' AND first-name = 'Donette';
- real
- bit
- decimal
- numeric
Q14. You need to remove all data from a table name Products. Which query fully logs the removal of each record?
- TRUNCATE FROM Products *;
- DELETE FROM Products;
- DELETE * FROM Products;
- TRUNCATE TABLE Products;
- 0.5
- error
- 0
- 2
- float
- int
- tinyint
- bigint
SELECT 'abc\
def' AS Result;
- abc\def
- abcdef
- error
- abc def
- SELECT TOP(1) first_name, last_name FROM Students ORDER BY NEWID();
- SELECT TOP(1) RAND(first_name, last_name) FROM Student;
- SELECT TOP(1) first_name, last_name FROM Student;
- SELECT TOP(1) first_name, last_name FROM RAND(Student);
https://www.petefreitag.com/item/466.cfm
DECLARE @MyVariable int;
SET @MyVariable = 1;
GO
SELECT @MyVariable;
- error
- 1
- null
- @MyVariable
- ALTER USER Sharon WITH DEFAULT_SCHEMA = Sales;
- ALTER USER Sharon SET SCHEMA Sales;
- CREATE SCHEMA Sales SET OWNER Sharon;
- CREATE SCHEMA Sales AUTHORIZATION Sharon;
Q21. The result of a CROSS JOIN between a table with 4 rows, and one with 5 rows, will give with ____ rows.
- 1024
- 20
- 0
- 9
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______ ;
-
WHERE SerialNumer LIKE '%10_3'
-
WHERE SerialNumer LIKE ('%10'+'_'+'3')
-
WHERE SerialNumer LIKE '%10"_"3'
-
WHERE SerialNumer LIKE '%10[_]3'
Q23. When no join type between multiple tables in a query's FROM clause is specified, what type of join is assumed?
- INNER
- RIGHT
- LEFT
- FULL
- 1 byte
- 2 bytes
- 4 bytes
- 8 bytes
- that only records from the rightmost table will be displayed
- that no records from the rightmost table are displayed if the records dont have corresponding records in the left table
- that records from the rightmost table will be displayed only if the records have a corresponding value in the leftmost table
- that all records from the rightmost table are represented in the result, even if there are no corresponding records in the left table
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;
-
studentid firstname lastname 1 1001 mark twain
-
studentid firstname lastname 1 1 mark twain
-
studentid firstname lastname 1 1000 mark twain
-
studentid firstname lastname 1 null mark twain
create table students( studentname varchar(50), grade int);
- select studentname from students where grade=max(grade);
- select top(1) studentname from students order by grade;
- select top(1) with ties studentname from students order by grade desc;
- select studentname,max(grade) from students order by grade desc;
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.
select bookid, boooktitle, bookauthor,quantityonhand from inventory.books;
- you only want to see results from books currently in inventory
- it instructs the query engine to find the books table in the inventory schema
- it instructs the query engine to find the books table in the inventory database
- it instructs the query engine to join the books table to the inventory schema
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
- Only records that have corresponding entries in table1 and table2 are displayed.
- No records from table1 are ever displayed.
- All records from table1 are displayed, regardless of whether the records have a corresponding row in table2
- Only records that have no corresponding records in table1 or table2 are displayed.