use tempdb go
--a) here primary key id defined inside column definition
CREATE TABLE Country( country_id int IDENTITY(1,1) CONSTRAINT [PK_Country_C_id] PRIMARY KEY CLUSTERED,
name varchar(50) NOT NULL)
drop table Country
--b) --here primary key is defined at the end
CREATE TABLE Country( country_id int IDENTITY(1,1), name varchar(50) NOT NULL,
CONSTRAINT [PK_Country_C_id] PRIMARY KEY CLUSTERED(country_id))
drop table Country
--c) here the constraint name will be generated by SQL Server (constraint constraint_name is optional) CREATE TABLE Country( country_id int IDENTITY(1,1), name varchar(50) NOT NULL, PRIMARY KEY CLUSTERED(country_id))
drop table Country
--d) here constraint name will be generated by SQL Server, clustered index is created by default
--Question 3
CREATE TABLE Country( country_id int IDENTITY PRIMARY KEY, name varchar(50) NOT NULL)
a) ALTER TABLE Country ADD COLUMN region_id integer not null --correct because add column is not valid syntax
b) ALTER TABLE Country ADD region_id integer not null --incorrect because it allows to add a new column
c) ALTER TABLE Country ADD COLUMN region_id integer not null -- correct because add column is not valid syntax
d) ALTER TABLE Country ADD region_id integer primary key --correct because the table can have only one primary key
--Question 4
a) BACKUP DATABASE [demo] TO DISK = N'c:\backup\demo.bak' WITH COPY_ONLY, NOINIT --correct noinit adds backup to the file, copy_only does not affect future sequence of backups
b) BACKUP DATABASE [demo] TO DISK = N'c:\backup\demo.bak' WITH INIT, DIFFERENTIAL --not correct - differential is used to perform differential backup, not full backup
c) BACKUP DATABASE [demo] TO DISK = N'c:\backup\demo.bak' WITH INIT ----correct init overwrites existing backups by a new backup
d) BACKUP DATABASE [demo] TO DISK = N'c:\backup\demo.bak' WITH COPY_ONLY -- --correct full backup with copy_only does not affect future sequence of backups
--Question 5
c) is correct - it fails - only data file is specified (data and log files are required to create a dataabse) d) is correct - it fails - log file must be defined after LOG ON which is missing
--Question 6
a) incorrect because no differential keyword, compressed invalid keyword, it should be compression b) correct because it involves two required keywords: differential and compression
c) incorrect - lack of differential and compression
d) correct - involve differential and compression key words
--Question 7
a) incorrect - involve differential key word
b) correct - we have backup log statement with the compression option c) incorrect - compressed is not valid (it should be compression instead)
d) correct - we have backup log statement with the compression option
--Question 8
a) correct because recovery means restore databases, perform its recovery and then open for users b)incorrect because "norecovery" leaves the database in the restoring state (database is not open for users)
c)incorrect because there is no "RESTORE AND RECOVER" statement in T-SQL d) correct because "with recovery" is the default option of the restore statement
--Question 9
a) correct because it is valid statement to grant read access to the table b)incorrect - there is no statement like grant read... (grant select ...is valid) c) correct because object:: is optional in grant select statement
d) incorrect because it grants more (read access to all tables in the HR schema) than we need
--Question 10
login is required to have access to SQL Server (this is server level object)
database user is created for login in a given database to ensure access to that database login can have many users associated with it, one user is connected to only one login
a)incorrect - db_ddladmin database role does not grant full admin rights in the dataabses - only allows for
creating, altering, dropping database objects like tables, procedures, etc.\
b) correct - execution of the procedure sp_addrolemember 'db_owner', 'adam' allows to add adam to the db_owner database role
which grants full administrative permissions for that database
c) incorrect - insert, update, delete, select do not involve full admin permissions
d) correct - it uses new syntax (allowed starting from SQL Server 2012) to add user to the db_owner database role
--Question 11
a) Correct statement to grant execute permissions on all procedures in the HR schema b) Incorrect - schema key word is missing
c) Incorrect - the ddl_admin database role allows to create new database objects, not to run procedures of other users
d) Incorrect - grants more than we need - execution of procedure from all schemas
--Question 12
a) Incorrect – stopping SQL Server affects other databases, no need to stop SQL Server in order to move user defined databases
b) incorrect – Action 5 is missing, it should be placed before or after action Action 6. Besides Action 7 is not needed
c) incorrect – no need to stop sql server
d) correct – it represents sequence of actions required to move any user defined database Action 2, Action 5, Action 6, Action 3 = Action 2, Action 6, Action 5, Action 3
--Question 13
a) incorrect – to grant read permissions we use grant select… (not grant read….) b) incorrect – except keyword is not valid in the grant select statement
c) correct – at first we grant select permissions on the whole schema Production. Then we exclude select permission on the table Product
DENY SELECT ON OBJECT::Production.Product TO Emily = DENY SELECT ON Production.Product TO Emily d) incorrect because
ALTER ROLE db_datareader ADD MEMBER Emily
Grants select permissions on the whole database (on all its schemas). It was requested to grant select only on the Production schema.
--Question 14
a) incorrect because first and second transaction log backups have been not restored.
b) correct because tail log backup is done in first step, next all backups are restored in the correct order with no recovery option. Last backup (tail log backup) is restored with recovery which is default option.
restore log demoRestore from disk = 'c:\master\demoRestore_tail_log_backup.trn' =
restore log demoRestore from disk = 'c:\master\demoRestore_tail_log_backup.trn' with recovery
c) incorrect because tall log backups has not been taken in the first step. It must be also restored as last backup.
d) correct – tail log backup taken in first step, all backups restored in correct order with no recovery option. Last statement allow to perform only database recovery.
--Question 15
a) incorrect because not possible to run Action 5 id SQL Server is stopped
b) correct – at first we should update database catalog by using alter database tempdb modify file.
After that SQL Server should be restarted. Tempdb files will be created automatically by SQL Server (no need to move files using OS commands from old location to a new one). Such procedure is only valid for tempdb database.
c) incorrect because it does not involve Action 5 which is absolutely required. Besides to need to copy/move files in case of the tempdb database
d) incorrect – it is not allowed to put system databases (tempdb, master, model, msdb) in the offline state
--Question 16
a) incorrect because at first we have to close open sessions. If session are opened SQL Server cannot obtain exclusive lock on the database which is required to restore that database
b) Incorrect because terminate open sessions is not valid statement in the alter database command c) incorrect because replace is missing in the restore statement. Replace is required to overwrite the existing database by a fresh backup and avoid taking tail log backup.
d) correct because we introduce database to the offline state (rollback immediate option automatically close all sessions and rollback their transactions if there are any in progress). Then we run restore command with the replace option
--Question 17
a) correct because “ALTER DATABASE [prod] SET RECOVERY FULL” is valid statement to switch to the full recovery model. In the second step full backup must be taken (it is called base backup)
b) incorrect because switch to full is not valid statement
c) incorrect – full backup is required as the second step to start with transaction log backups and it is missing
d) correct – the same explanation like in a) (no init is the default backup statement option – it appends new backups to the file if it exists on the disk)
--Question 18
All answers are correct (all of them will fail) because it is not allowed to add new files for the model and master database. We can add files to tempdb, msdb, any user defined databases.
--Question 19
a) and c) are incorrect since we can have only one clustered index on the table (data can be ordered in only one way)
b) and d) are correct (create index….=create nonclustered index…). Both statements cerate clustered indexes on the last_name column. The last level of the indexes will involve also
(emp_id,birth_date,hire_date). In this way the whole query can be executed based only on the data included in the index.
--Question 20
a) correct – by default clustered index is created on primary key column b) correct – by default nonclustered unique index is created on unique column c)incorrect – non clustered index is not created by default
d)incorrect – no index created for check constraint
