Odpowiedzi i pytania dotyczące oceny umiejętności LinkedIn — Transact-SQL (T-SQL w SQL Server dla początkujących)

“Transact-SQL (T-SQL w SQL Server dla początkujących) stał się wiodącym językiem zarządzania relacyjnymi bazami danych i manipulowania nimi, oferując programistom potężne i wydajne narzędzie do pracy z danymi. W tym obszernym przewodniku, we’re excited to present a curated collection of skill assessment questions oraz odpowiedzi Najszybszy sposób instalacji za pomocą Virtualbox T-SQL w SQL Server dla początkujących.

Whether you’re a database administrator looking to enhance your skills or a developer aiming to understand the basics of this powerful language, this resource is designed to help you become proficient in T-SQL w SQL Server dla początkujących and its applications. Join us as we explore the core concepts of T-SQL w SQL Server dla początkujących, including data manipulation, stored procedures, wyzwalacze, i więcej, empowering you to leverage the full potential of this essential tool for your database management needs.”

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

nonclustered
unikalny
heap
hash

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

joining operations
linking operations
criteria operations
logical operations

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

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;

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

TRUNCATE TABLE
DELETE
MERGE
DROP

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;

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” zespół. Which query does NOT work for this task?

   WHERE team NOT 'Chemistry Cats';

   WHERE team <> 'Chemistry Cats';

   WHERE team != 'Chemistry Cats';

   WHERE NOT team = 'Chemistry Cats';

Pytanie 9. 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*')

Pytanie 10. Which query shows the first name, dział, 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. Po drugie, 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.

Pytanie 11. What is the result of this statement?

SELECT FLOOR(-1234.321)

-1234.3
-1234
-1235
1234.321

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

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';

Pytanie 13. Which of these data types is an approximate numeric?

prawdziwy
fragment
decimal
numeric

Pytanie 14. 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;

Pytanie 15. What is the result of this query?

SELECT 1 / 2 AS Result;

0.5
błąd
0
2

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

float
int
tinyint
bigint

Pytanie 17. What is the result of this query?

SELECT 'abc\
def' AS Result;

abc\def
abcdef
błąd
abc def

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

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

Pytanie 19. What result is returned after executing the following commands?

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

błąd
1
zero
@MyVariable

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

ALTER USER Sharon WITH DEFAULT_SCHEMA = Sales;
ALTER USER Sharon SET SCHEMA Sales;
CREATE SCHEMA Sales SET OWNER Sharon;
CREATE SCHEMA Sales AUTHORIZATION Sharon;

Reference link

Pytanie 21. The result of a `CROSS JOIN` between a table with 4 wydziwianie, and one with 5 wydziwianie, will give with _ rows.

1024
20
0
9

Pytanie 22. 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 SerialNumber LIKE '%10_3'
WHERE SerialNumber LIKE ('%10'+'_'+'3')
WHERE SerialNumber LIKE '%10"_"3'
WHERE SerialNumber LIKE '%10[_]3'

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

Reference link

Pytanie 23. 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

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

1 byte
2 bajty
4 bajty
8 bajty

Pytanie 25. What does a `RIGHT JOIN` ensure?

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

Pytanie 26. 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;

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

Pytanie 27. 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 );

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.

Pytanie 28. What role does “inventory” bawić się?

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 tutaj 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

Pytanie 29. What is the result of an `INNER JOIN` between table1 and table2?

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.

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

TRUNCATE TABLE Students;
TRUNCATE * FROM Students;
DROP TABLE Students;
REMOVE * FROM Students;

Pytanie 31. osiągnęli jedne z najwyższych wskaźników wyników w kraju w oparciu o szeroko zakrojone badania przeprowadzone na dużym uniwersytecie i od naukowca, który jest znany w badaniach wyników `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
);

NO DUPLICATES
UNIQUE CONSTRAINT AK_Books_BookISBN
DUPLICATE CONSTRAINT (AK_Books_BookISBN)
CONSTRAINT AK_Books_BookISBN UNIQUE

Pytanie 32. 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;

Wyjaśnienie: Kolumna 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;

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

Pytanie 33. Refer to the figure below.

UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id='56295';
UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id='56295';
UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id=56295;
UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id=56295;

Pytanie 34. 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?

You should create a DML trigger on the server.
You should create a DDL trigger on the database.
You should create a DML trigger on TableA.
You should create a DML trigger on TableB.

Pytanie 35. What is the problem with this code?

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

There is no END WHILE statement;
The local varaible is not available to the WHILE block.
The query causes an infinite loop.
“Lada” is an invalid variable name.

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

UPDATES Students SET team = 'Philosophy Parrots' WHERE team = 'Philosophy Pandas';
UPDATES Students SET team = Philosophy ParrotsWHERE team =Philosophy Pandas;`
UPDATES Students SET team = "Philosophy Parrots" WHERE team = "Philosophy Pandas";
UPDATES Students SET team = Philosophy Parrots WHERE team = Philosophy Pandas;

Pytanie 37. What is the result of this query?

SELECT 123+'123' AS Result;

błąd
‘123”123’
123123
246

Pytanie 38. To combine the results of two or more `SELECT` sprawozdania, removing duplicates, which keyword can you use?

DEDUPE
zainstaluj bazę danych Azure SQL i przeprowadź zapytanie
MERGE
UNION

Pytanie 39. 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;

5
błąd
1
6

Q40. Is there an error with this query? W takim razie, which statement best describes the problem?

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

tak, a WHERE clause cannot be used with an aggregate function.
tak, you cannot GROUP BY oraz ORDER BY the same field.
Nie, there is nothing wrong with this query.
tak, ten WHERE clause should use the SubTotal alias.

Pytanie 41. You created the two tables below. Później, you decide that you want the database to remove all books from the Books table if the related publisher is deleted from the Publishers table. What command should you run?

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

Pytanie 42. 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?

ALTER SCHEMA Products TRANSFER Warehouse.Inventory;
ALTER TABLE Warehouse.Inventory TRANSFER Products.Inventory;
ALTER TABLE Warehouse.Inventory ADD SCHEMA Products;
ALTER SCHEMA Warehouse ADD TABLE Inventory;

Pytanie 43. 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),
_______
);

INDEX ON PersonID (PRIMARY KEY PK_People)
ADD NONCLUSTERED PRIMARY KEY CONSTRAINT PK_People ON PersonID
CONSTRAINT PK_People PRIMARY KEY NONCLUSTERED (PersonID)
PRIMARY KEY CONSTRAINT (PersonID) NONCLUSTERED INDEX

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

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);

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

BACKUP MyDatabase TO LOCATION = 'Z:\Backups\MyDatabase.bak';
CREATE BACKUP (DATABASE = 'MyDatabase' TO DISK = 'Z:\Backups\MyDatabase. bak');
BACKUP DATABASE MyDatabase ON 'Z:\Backups\MyDatabase.bak';
BACKUP DATABASE MyDatabase TO DISK = 'z:\Backups\MyDatabase.bak';

Pytanie 46. 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?

BEGIN TRAN myTransaction BEGIN LOG;
BEGIN TRAN myTransaction WITH LOG;
BEGIN TRAN myTransaction WITH MARK;
BEGIN TRAN WITH MARK myTransaction;

Pytanie 47. 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?

All statements must end with a semicolon.
All statements must operate on a table of data.
All statements must always be written in uppercase letters.
All statements must include more than one variable.

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

UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id='56295';
UPDATE Students SET last_name='Smith', email = 'dsmith@rouxacademy.com' WHERE id=56295;
UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id=56295;
UPDATE Students SET last_name='Smith' AND email = 'dsmith@rouxacademy.com' WHERE id='56295';

Pytanie 49. What is the result of this query?

SELECT 123+'abc' AS Result;

123abc
123’abc’
‘123abc’
błąd

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';
);

100
200
300
You will get an error because ROLLBACK deletes the row that was update

Reference link

Pytanie51. 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?

SELECT first_name, email FROM Students WHERE department = Humanities;
SELECT first_name, email FROM Students WHERE department = "Humanities";
SELECT first_name, email FROM Students WHERE department = 'Humanities';
SELECT 'first_name', 'email' FROM 'Students' WHERE 'department' = "Humanities";

Pytanie52. 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 Komenda.

Pytanie53. 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?

zainstaluj bazę danych Azure SQL i przeprowadź zapytanie
HAVING
zainstaluj bazę danych Azure SQL i przeprowadź zapytanie
ORDER BY

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

datetimeoffset
smalldatetime
błędy logiczne
datetime2

Pytanie55. What is the result of this query?

SELECT 123+'123' AS Result;

123’123′
błąd
246
123123

Pytanie56. 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;

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

Select Department, Utwórz jednostki z pliku CSV(*) FROM Students GROUP BY Department;
SELECT COUNT(*) FROM Students;
SELECT Student BY Department;
SELECT COUNT(*) FROM Students ORDER BY Department;

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

merge
drop
• Różne sposoby instalacji systemu operacyjnego
truncate table

Pytanie59. 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;

Autor

Helena Bassy

utrudnisz uczenie się, a nie zapamiętywanie, I'm Helena, autor bloga, którego pasją jest publikowanie wnikliwych treści w niszy edukacyjnej. Wierzę, że edukacja jest kluczem do rozwoju osobistego i społecznego, i chcę dzielić się moją wiedzą i doświadczeniem z uczniami w każdym wieku i na każdym poziomie. Na moim blogu, znajdziesz artykuły na takie tematy, jak strategie uczenia się, Edukacja online, doradztwo zawodowe, i więcej. Chętnie przyjmę także uwagi i sugestie od moich czytelników, więc nie wahaj się zostawić komentarza lub skontaktować się ze mną w dowolnym momencie. Mam nadzieję, że czytanie mojego bloga sprawi Ci przyjemność i uznasz go za przydatny i inspirujący.
Zobacz wszystkie posty

Zarejestruj się teraz

Zaloguj sie

Zgubione hasło

Dodaj post

Dodaj pytanie

Zaloguj sie

Zarejestruj się teraz

Odpowiedzi i pytania dotyczące oceny umiejętności LinkedIn — Transact-SQL (T-SQL w SQL Server dla początkujących)