1

Topic: SQL request

Hello, there are tables; persons, status, dept, posts. Foreign keys status, dept, posts are in persons. It is required to deduce  the employee (persons), the status name (status), the department name (dept), the post name (posts), reception and dismissal dates. While it turned out:
SELECT CONCAT (last_name, ' ', SUBSTRING (first_name, 1,1), '. ', SUBSTRING (second_name, 1,1), '. ')
AS ' fio ', status.name AS ' status ', dept.name as dept, posts.name AS ' post ', date_employ, date_uneploy
FROM status, dept
LEFT JOIN persons ON persons.id_dept = dept.id
LEFT JOIN posts ON persons.id_post = posts.id
WHERE status.id = persons.id_status
Prompt, usage WHERE for communication of tables is how much justified and it is possible as  in this case
To use JION instead of WHERE.

2

Re: SQL request

1. It is justified
2. It is possible

3

Re: SQL request

Vilkas;
If it is short for LEFT JOIN WHERE not equivalent ON

4

Re: SQL request

Vilkas wrote:

Prompt, usage WHERE for communication of tables is how much justified and it is possible as  in this case
To use JION instead of WHERE.

- where it is fulfilled after join
- join <table> on p11 = p12 and p21 = p22

5

Re: SQL request

The grandfather;

wrote:

- where it is fulfilled after join

6

Re: SQL request

TaPaK wrote:

the Grandfather;
it is passed...

And  not so?
Well, if to add to the left of the word "logically", certainly "is fulfilled".

7

Re: SQL request

Vilkas;
However, it is extremely undesirable FROM to use in one and a comma and JOIN!
About it it is possible to esteem at Microsoft in the documentation.
Better to say, throw out a comma from FROM somewhere far away. And never it there use.

8

Re: SQL request

Paraphrasing the known citation - "and the optimizer - a subject dark., and to research is not subject"
It it is finite can  a label before connection, but that can, and can and is not present.

9

Re: SQL request

FROM persons P
LEFT JOIN status S ON P.id_status=s.id
LEFT JOIN dept D ON P.id_dept=D.id
LEFT JOIN posts PS ON P.id_post=PS.id

10

Re: SQL request

TaPaK wrote:

the Grandfather;
it is passed...

Like as it is correct

11

Re: SQL request

It is possible and to esteem here: https://msdn.microsoft.com/ru-ru/library/ms189499 (v=sql.100).aspx

12

Re: SQL request

wrote:

it is passed...
Like as it is correct

You are boring. For example so

CREATE TABLE Drops (id int PRIMARY KEY)
INSERT INTO Drops (Id) SELECT ROW_NUMBER () OVER (ORDER BY 1/0) FROM master. spt_Values
SELECT
*
FROM
Drops a
INNER JOIN
Drops b
ON
a. Id = b. Id
WHERE
b. Id> 2
DROP TABLE Drops

13

Re: SQL request

Help here with it what there will be right answers:
1 It is necessary to write Transact-SQL request to the table which will cause the function returning table data type on every line initial table. What operators or language oriented functions Transact-SQL should be used?
UNION.
FOR XML.
INNER JOIN.
Trigger.
CAST.
OUTER JOIN.
CROSS APPLY.
FORMAT.
2 Company has retail shops in several big cities of the country. It is necessary to receive a name list of customers which live in cities where there are shops of the company sorted by a city title, and as no title of these cities. What of requests returns the demanded information?
SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCrty NOT EXISTS (SELECT StoreCity FROM Stores) ORDER BY Customer City
SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCrty <> ALL (SELECT StoreCity FROM Stores) ORDER BY StoreCity
SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity <> ANY (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores) ORDER BY StoreCity
It is necessary for 3 you to change an account from which starts a SQL Server service. You will use what toolkit:
Windows Services tool
Operator ALTER LOGIN
SQL Server Configuration Manager toot
sc.exe config "MSSQLSERVER" obj = "DOMAIN\User" password = "password"
4 With a view of support of a principle of the least privileges and simplification of the subsequent administration of a copy of a SQL Server it is necessary for you to select an account from which will start a SQL Server service. Select a variant:
Domain user account.
Managed service account.
Local System account.
Network Service account.
5 you migrate a database on new SQL the server. You try to add [Corpnet\User1] login in a database. Nevertheless you receive the following error report: "User already exists in current database." It is necessary for you to configure [Corpnet\User1] so that it got access to a database with the same powers what were at it on the initial server. Select a variant:
DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User 1];
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Corpnet\User1];
ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
6 you administer a SQL Server. Service MSSQLSERVER starts from domain account DOMAIN\SQLService. You plan to use Instant File Initialization for reduction of time delays of operations of automagnification of the size of files of databases. What should you make? Select everything that approaches:
Restart of SQL Server Agent Service.
To disconnect a mode snapshot isolation.
Restart the SQL Server Service.
To assign the DO MAI N\SQLService to an account right Perform Volume Maintenance Tasks in local trust relationships policies of an operating system.
To add DOMAI N\SQLService an account in the fixed server role Server Operators.
To include a mode snapshot isolation.
7 you administer two SQL Servers of the server. Servers are in various not confidential domains. You plan to configure a mode of mirroring of databases (database mirroring). It is necessary for you to configure finishing points (database mirroring endpoints) on both servers. What should you make?
To configure a SQL Server service to use for start Network Service an account.
To use the server certificate.
To configure a SQL Server service to use for start Managed service an account.
To configure a SQL Server service to use for start Local System an account.
8 you administer a company database, users complain that SQL application Server works slowly. You should check up the information on processor loading, disk usage and storage usage. Specify the right answer?
You should check up appropriate dynamic views of control (DMVs).
You should execute commands of update of statistics.
You should check up model of recovery of a database master.
9 you the manager of a copy of Microsoft SQL Server. You should check up physical integrity of a database. What should you do?
To fulfill sp_helpfile.
To fulfill DBCC CHECKDB.
To check up in basis MSDB the table suspect_pages.
To recover a database from the last full backup copy. To apply any differential copy and transaction log backup copies.
10 you the manager of a SQL Server from a database with name DB1. The last differential backup is fulfilled at 15:30, the full backup copy has been fulfilled at 13:30, and quick pictures of a database have been created at 16:30 and at 17:00. Backup copies and quick pictures of a database are stored on other disk. At 17:05 there was a failure. You should recover DB1 minimum loss the data. You should fulfill what actions to reach this purpose?
You should recover the full backup copy
You should recover a database picture on 16:30 hours.
You should recover a database picture on 17:00 hours
You should recover as the full backup copy and differential backup.
11 you work and the companies which is called Wiikigo orp. The company uses a SQL Server. You the manager of a database of the company, in the company exists a database with name DB1. It is necessary for you to boil thoroughly according to company requirements that the database can be recovered for the certain moment of time. What actions should be fulfilled to reach this purpose?
You should be convinced that the simple model of recovery in a database is used.
You should be convinced that the complete recovery model in a database is used.
You should be convinced that the option checksum in a database is used.
You should be convinced that the recovery model  is used by incomplete recording in a database.
12 you work in the company which uses a SQL Server. You the manager of databases of the company. Job with name Delivery List it is created by you. This job demands access on record to a file on a file server, but job cannot get access to a file server. You intend to adjust service of the agent of a SQL Server so that only the service of the agent of a SQL Server had access on a read and write to a file server. You should use what account type?
You, should use the security account local service
You, should use the security account network Service
You, should use the domain security account
You, should use the security account local System
13 you control a copy of a SQL Server which includes a database with name Test1. In your absence, the user with login Clark will control quick pictures of databases. You should be convinced that all necessary rights for the user are given to delete pictures of database Test1. Specify the right answer?
You should grant right UPDATE
You should grant right CREATE DATABASE
You should grant right DROP DATABASE
You should grant right INSERT ANY DATASPASE
14 you administer Microsoft SQL Server 2012 When transaction logs grow, the SQL Server should send the message of electronic mail for managers of databases. You need to adjust a SQL Server for sending of the message of electronic mail. What should you adjust?
SQL Mail
An Extended Events session
Alerts and operators in SQL Server Agent
Policies under Policy-Based Management
15 you administer a database of Microsoft SQL Server 2012, you give temporary access securityadmin to User1 on a database server. You need to know, whether adds User1 logins in securityadmin. At level of the server you should use what group of actions of audit?
SERVER_STATE_CHANGE_GROUP
SERVER_PRINCIPAL_IMPERSONATTON_GRQUP
SUCCESSFUL_LOGIN_GROUP
SERVER_ROLE_MEMBER_CHANGE_ GROUP
16 you administer a copy of Microsoft SQL Server 2012 which contains the financial database allocated in a network of data storage (SAN). The financial database has following characteristics; the data file in volume of 2 terabytes is allocated on selected LUN (disk D). The Transaction log 10  is allocated on selected LUN (disk). Disk D has 1 terabyte of an empty seat on a disk. Disk  has 5  an empty seat on a disk. The database permanently changes users in working hours from Monday till Friday between 09:00 and 17:00. Five percent of the existing data change every day. The financial department loads big CSV - files in some tables every working day and 11:15 and 15:15 by means of commands  or BULK INSERT. Each loading of the data adds 3  the data in a database. These operations of loading of the data should be fulfilled and a minimum time interval. The full backup copy of a database is fulfilled every Sunday  10:00. Reserve operations will be fulfilled each two hours (11:00, 13:00,15:00 and 17:00) in working hours, it is necessary for you to be convinced that reserve to cats will be continued, if there will be an incorrect checksum. You should use what variant of reserve copying?
STANDBY
Differential
FULL
CHECKSUM
BULK_LOGGED
CONTINUE_AFTER_ERROR
SIMPLE
DBO_ONLY
COPY_ONLY
SKIP
RESTART
The transaction log
NO_CHECKSUM
NORECOVERY
17 you administer a copy of Microsoft SQL Server 2012 which contains a financial database,  and data storage networks (SAN). The financial database has following characteristics: the Data file in volume of 2 terabytes is allocated on manufactured LUN (disk D). The Transaction log 10  is allocated on selected LUN (disk). Disk D has 1 terabyte of an empty seat on a disk. Disk  has 5 ; an empty seat on a disk. The database permanently changes users in working hours from Monday till Friday between 09:00 and; 17:00. Five percent of the existing data everyone a tribute change. The financial department loads the big CSV-files  some tables every working day at 11:15 and 15:15 by means of commands  or BULK INSERT. Each loading of the data adds 3  the data in a database. These operations of loading of the data should be fulfilled in a minimum time interval. The full backup copy of a database is fulfilled every Sunday at 10:00. Reserve operations will be fulfilled each two hours (11:00, 13:00, 15:00 and 17:00) in working i time. In the environment at 10:00 the design team asks you to update a database on development servers, using most current release.
It is necessary for you to fulfill the full backup copy of a database which will be recovered on the development server. You should use what variant of reserve copying?
NORECOVERY
FULL
NO_CHECKSUM
CHECKSUM
Differential
BULK_LOGGED
STANDBY
RESTART
SKIP
Transaction log
DBO ONLY
COPY_ONLY
SIMPLE
CONTINUE AFTER ERROR
18 you administer two copies of Microsoft SQL Server 2012. You unroll application which uses a database for a named instance (named instance). Application cannot be connected to  the data no named instance. You should be convinced that application can be connected to named instance. What should you make?
Use Client Data Quality for application adjustment.
Launch service of SQL Server Browser Service.
Launch service of SQL Server Integration Services Service.
Use Master Data Services Configuration Manager for application adjustment.
19 you administer a database of Microsoft SQL Server 2012. In a database there is a table with name Customers, belonging UserA, and other table with name Orders, belonging UserB, at you also is stored procedure with name GetCustomerOrderlnfo, belonging UserB, GetCustomerOrderlnfo selects the data from both tables. You create the new user with name UserC. You should be convinced that UserC can cause stored procedure GetCustomerOrderlnfo. Also it is necessary for you to assign only minimum necessary resolutions for UserC. Or resolutions you should assign what resolution UserC? Select everything that is applicable.
Resolution Select for Customers
Resolution Execute on GetCustomerOrderlnfo Resolution Take Ownership for Customers
Resolution Control for GetCustomerOrderlnfo Resolution Take Ownership for Orders
Resolution Select for Orders
20 you use a database with name ContosoDb in the domain. It is necessary for you to create the user who can enter (log on) into database ContosoDb. Also it is necessary for you to be convinced that you can transfer a database on different servers of databases in the domain without additional adjustments of the user account. You should create what type of the user?
The user is compared with the certificate
User SQL without an input
The user of the domain
User SQL with login