Step by Step Log Shiping Configuration

SQL Server Built-in Functionality

There are two options for configuring log shipping using the updated feature in SQL Server 2005.

You can either use SQL Server Management Studio or stored procedures. Using the stored procedures

to configure log shipping was not possible in SQL Server 2000. Log shipping is included as

a feature in the following editions of SQL Server 2005: Developer Edition, Enterprise Edition, Standard

Edition, and Workgroup Edition. In SQL Server 2000, log shipping was only available in the

Developer and Enterprise Editions.

Like SQL Server 2000, SQL Server 2005 provides a way to monitor the status of your log shipping

pair by configuring a monitor server during the configuration of log shipping. The section “Monitoring

Log Shipping” later in this chapter shows you how to see the status of log shipping. The only reason

I am mentioning it here is that where you place the monitor is a consideration for installation. It

can be placed on any available SQL Server 2005 instance that supports the log shipping feature. I do

not recommend placing it on your primary, as that would be a single point of failure. I always recommend

putting the log shipping monitor functionality on a completely separate server that is not the

primary or the secondary. The monitor is optional; so if you choose not to deploy one, there are other

ways of monitoring the status of log shipping. One bonus of a monitor that exists on another SQL

Server instance is that multiple log shipping configurations can share one log shipping monitor.

SQL Server Management Studio

The following steps walk you through the process of configuring log shipping via SQL Server Management Studio:

Step 1. Start SQL Server Management Studio. Expand the Databases folder, select the database that will serve as the primary, right-click the database, select Tasks, then Ship Transaction Logs

as shown in Figure 1. You can also select Properties, and then when the properties of

the database are displayed, select the Transaction Log Shipping option. With either method,

Step 3. Click Backup Settings. The Transaction Log Backup Settings dialog will be displayed. In the

Network Path to Backup Folder text box, enter the share name you created on the primary

computer. If you are planning on crossing domains that are not trusted for log shipping, you

will have to use the IP address instead of the server name in the share (e.g., \\172.22.10.1\

backups). If the folder is located on the same server as the SQL Server instance containing

the primary database, enter the path to the local backup path share. This is not necessary if

you are generating backups to another server or share (as I noted previously in the considerations

for log shipping). By default, the log shipping functionality will create a backup job

on the primary with the name LSBackup_dbname, where dbname is the name of the primary

database. If you wish to change the name of the backup job, change Job Name, as

shown in Figure3

The backup job defaults to a frequency of 15 minutes. If you want to

change how often the backup job is executed, click Schedule. You will see a dialog similar

to the one in Figure 4.

Step 4. Click Add to begin the process of adding a secondary. You will see the Secondary Database

Settings dialog as shown in Figure 6

Click Connect. You will be prompted to connect to

the secondary database instance as shown in Figure 7.

Make sure that you enter the

proper name or IP address of the SQL Server 2005 instance that will contain the warm

standby database and click Connect.

Step 5. Select the Initialize Secondary Database tab of the Secondary Database Settings dialog. By

default, the name of the database that will be used on the secondary SQL Server instance is

the same as the one on the primary. You have three options:

• Use the configuration tool to create a full backup of the primary database as shown

in Figure 8

You cannot control the name of the backup that is generated; it will

be dbname.bak, and will be placed in the directory or share you configured in step 3.

If you want to restore the database to a different path on the secondary, click Restore

Options, and enter the new paths for your data and log files in the dialog as shown in

Figure 9

. The paths you enter should be local to the secondary server, such as

C:\SQL Data. If you want to restore the database to a different name, enter a new

name in the Secondary Database drop-down/entry box.

figure 10

• Use an existing backup file and have the process restore it on the secondary server. An

example is shown in Figure 10.

 As with the previous option, you can choose to customize

where you will place the data and log files on the secondary. Click Restore Options.

Note Both options for initializing your standby database are controlled by the settings you will configure on the

Restore Transaction Log tab for restoring your transaction log files. For more information, see step 7.

• Back up, copy, and restore the database on the secondary yourself with either

figure 11

NORECOVERY or STANDBY. Select No, the Secondary Database Is Initialized, as shown in

Figure 11

. You must select the database you restored in the Secondary Database

drop-down.

Step 6. Select the Copy Files tab. Enter the share name you created on the secondary to receive

the backup files. The log shipping functionality can

also delete the files copied to the secondary. The default is three days (72 hours). The

default job created on the secondary (so the copy is a pull, not a push) is named

LSCopy_primary_dbname where primary is the name of the primary database instance

and dbname is the name of the database. To modify how often the copy job is run, click

Schedule. You will see a dialog similar to the one displayed in Figure 12.

 Click OK

Step 7. Select the Restore Transaction Log tab. The main option you will select here is to tell the

restore job to apply the transaction logs using either WITH NORECOVERY or WITH STANDBY. If

you previously selected the option to initialize the database, whatever you selected for the

restore state will also be used for the restoration of the full database backup. An example is

shown in Figure 14

. The default job created on the secondary (the copy is a pull, not a

push) is named LSRestore_primary_dbname where primary is the name of the primary

database instance and dbname is the name of the database. To modify how often the restore

job is run, click Schedule. You will see a dialog similar to the one displayed in Figure 15.

Click OK when finished modifying the schedule.

Note If you select the WITH STANDBY option for your restore, and the user configuring log shipping does

figure 16

not have the proper rights for both SQL Server instances, you should see a message similar to the one in

Figure 16.

step  8. Click OK on the Secondary Database Settings dialog to return to the main transaction log

shipping screen. To configure a monitor, select the option Use a Monitor Server Instance.

This is optional; however, as noted previously, if you do not create the monitor now, you

cannot add it later. The display will be similar to the one in Figure 17.

If you create a

monitor, click Settings. Otherwise, skip to step 10.

Step 9. On the Log Shipping Monitor Settings dialog shown in Figure 18,

 click Connect.

You will

be prompted to connect to the SQL Server instance that will contain the monitor. An example

is shown in Figure 19.

 Click Connect. You should now see the dialog updated, as

shown in Figure 20.

Select how the primary and secondary instances will connect to the

monitor. On the monitor, the alert job will be configured with the name LSAlert_InsName

by default, where InsName is the name of your SQL Server instance. You can change this if

you wish. Click OK to close the Log Shipping Monitor Settings dialog.

Step 10. Click OK to start configuring log shipping as shown in the dialog in Figure 21.

11. Once log shipping is successfully configured, you will see a dialog similar to the one in

Figure 22. Click Close.

Figure 22. Successful completed log shipping configuration

Transact-SQL

As with most features in SQL Server 2005, you can set things up either via SQL Server Management

Studio or through Transact-SQL. To configure log shipping, you will use the stored procedures

Stored Procedure Server

sp_add_log_shipping_primary_database Primary

sp_add_schedule Primary

sp_add_log_shipping_primary_secondary Primary

sp_add_log_shipping_alert_job Monitor (optional)

sp_add_log_shipping_secondary_primary Secondary

sp_add_jobschedule Secondary

sp_add_log_shipping_secondary_database Secondary

Note If you look in both the master and msdb databases, you may see other system stored procedures that

relate to log shipping (e.g., sys.sp_processlogshippingmonitorhistory). These procedures are used by log

shipping and are called within some of the stored procedures used for installing, running, and maintaining log

shipping. These are not documented since you would never actually use them yourself.

The easiest way to see how log shipping is scripted is to do it through SQL Server Management

Studio once and then generate a script. The stored procedures listed earlier .

in SQL Server 2005 Books Online, and going through them here does not make a lot of sense.

Custom Log Shipping

As noted earlier, you can also code your own log shipping process. I have written scripts for you to

use or use as an example; they can be found on the Apress site in the Source Code/Download section.

The log shipping process I wrote is a tad different than but similar to Microsoft’s built-in

feature. One of the main problems I have with canned features such as the built-in implementation

in SQL Server 2005 is that it requires you to change any maintenance you may have in place already.

You cannot just hook into an existing transaction log backup that is already being done. The scripts

on the Apress site allow you to use your existing transaction log backup job. You also do not have as

much control over the copy process since it is pretty much a black box. My scripts are a combination

of SQL Server stored procedures and VBScript that is used to perform the copying.

Postconfiguration Tasks

After you configure log shipping, you will need to ensure that all objects that live outside of the primary

database are also configured on your secondary (or secondaries if you are log shipping to more

than one server). If you do not do this, your database may not function properly if you ever need to

bring it online. The easiest way to do most tasks is to create scripts, modify them if necessary, and

apply them to the secondary instance.

Once you select the object to script, you will have three options: script to a query window,

script to a file, or script to the clipboard. The easiest option is to script the object directly to a file,

but if you are trying to create a master script to create all objects, you may want to script it to the

clipboard or to a query window and manipulate it that way.

If an object cannot be scripted from Management Studio, such as an SSIS package, you will

need to find what the best method is for getting that object over to the secondary. The easiest way is

always to get it into some sort of file in Windows and then copy it to the other server and apply it.

Administering Log Shipping

Once you get log shipping set up, you will need to administer and maintain it. There are two methods

for hiding the server name change in a log shipping role change: Network Load Balancing and

configuring a DNS alias. Since these techniques apply to more than just log shipping, they are covered.

configured on the same hardware as a failover cluster; so if any of the instances are nodes of a

failover cluster, you cannot use this method to abstract the instance name change.

Monitoring Log Shipping

One major change from SQL Server 2000 Enterprise Edition is the way you monitor log shipping.

SQL Server 2000 shipped with a Log Shipping Monitor that when installed showed you in an interface

the status of your log shipped pair. SQL Server 2005 removes the graphic interface, but you

can query tables or use stored procedures.

Job Status

Arguably the simplest way to first check the status of log shipping for most administrators will be

to see the status of the SQL Server Agent jobs involved with log shipping. Those will tell you if the

configured jobs are actually running successfully or are failing.

If you do not want to use any of the available stored procedures to monitor your log shipping configuration,

you can query the system tables (log shipping–specific tables, tables relating to jobs, etc.)

that contain information about log shipping directly. All of the tables are located in msdb. The tables

specific to log shipping are the following:

• msdb.dbo.log_shipping_monitor_alert: This table contains only one column, which is the

SQL Server Agent job ID of the log shipping alert job.

• msdb.dbo.log_shipping_monitor_error_detail: This table contains the specific SQL Server

Agent job errors encountered during log shipping.

• msdb.dbo.log_shipping_monitor_history_detail: This table contains the history details for

the SQL Server Agent jobs associated with the log shipping plans.

• msdb.dbo.log_shipping_monitor_primary: This table contains configuration information

about the primary database, and there is only one record per primary.

• msdb.dbo.log_shipping_monitor_secondary: Similar to the previous table, this contains one

record per secondary and has information about the configuration of the secondary.

• msdb.dbo.log_shipping_primaries: This table contains the monitor information for each log

shipping plan.

• msdb.dbo.log_shipping_primary_databases: This table contains one record per primary, and

has information about the primary database.

• msdb.dbo.log_shipping_primary_secondaries: This table contains one record per primary,

and has information about the secondary database.

• msdb.dbo.log_shipping_secondaries: This table has the mappings from the log shipping

plan to the secondary instance and database.

• msdb.dbo.log_shipping_secondary: This table contains information about the secondary and

its status. There is one record per database.

• msdb.dbo.log_shipping_secondary_databases: This table contains configuration information

about the secondary database. There is one record per configuration

Newly created SQL object Details for last Week

PRINT '=========================='
PRINT '     SQL objectDetails    '
PRINT '=========================='
select CONVERT(VARCHAR(35), name)"Name" ,xtype , crdate from  sysobjects  where crdate >= getdate() -7

Find out any Login created or updated in last week

PRINT '=========================='
print '   LOGIN UPDATE DETAILS   '
PRINT '=========================='
select CONVERT(VARCHAR(15), loginname) loginname,CONVERT(VARCHAR(10), dbname) dbname, updatedate  from master..syslogins   where  convert(varchar(8),updatedate,112)>   convert(varchar(8),getdate()- 7,112) and createdate <> updatedate
PRINT '=========================='
print '       LOGIN DETAILS      '
PRINT '=========================='
select CONVERT(VARCHAR(15), loginname) loginname, CONVERT(VARCHAR(10), dbname) dbname, createdate from master..syslogins   where convert(varchar(8),createdate,112) > convert(varchar(8),getdate()- 7,112)

find out New created Maintanance plan&Jobs in week

PRINT '===================================='
PRINT 'New created Maintanance plan in week'
PRINT '===================================='
select CONVERT(VARCHAR(25), plan_name)  plan_name,date_created 
from msdb..sysdbmaintplans 
where convert(varchar(8),date_created,112)> convert(varchar(8),getdate()- 7,112)
PRINT '=========================='
PRINT '  New created Job in week '
PRINT '=========================='
select * from  msdb..sysjobs where date_created > convert(varchar,getdate()-7,101)

Backup time details for database in Sql Server

PRINT '================================'
PRINT '    Backup time for database'
PRINT '================================'
SELECT database_name,backup_size,backup_start_date,backup_finish_date,convert(decimal(5,2),datediff(ss,backup_start_date,backup_finish_date)/60.00) "Backup time"
from msdb.dbo.backupset
where convert(varchar,backup_start_date,101)=convert(varchar,getdate()-1,101)
order by backup_start_date desc

'TOP TABLES IN EACH DATABASE in Sql Server

PRINT '================================'
PRINT 'TOP TABLES IN EACH DATABASE     '
PRINT '================================'
SET NOCOUNT ON
DECLARE @CMD NVARCHAR(4000)
PRINT 'Successful Jobs in the Instance'
set @CMD='select CONVERT(VARCHAR(35), b.name), run_date, a.step_id  from msdb..sysjobhistory a,msdb..sysjobs b where a.job_id = b.job_id and a.run_date = convert(varchar(8),getdate()- 1,112) and run_status = 0 and b.job_id in(select job_id from msdb..sysjobs)'
EXEC SP_EXECUTESQL @CMD
GO
DECLARE NM CURSOR LOCAL STATIC FOR
SELECT NAME FROM SYSDATABASES
DECLARE @DN VARCHAR(50)
DECLARE @CMD1 NVARCHAR(4000)
DECLARE @CMD2 NVARCHAR(4000)
DECLARE @CMD3 NVARCHAR(4000)
PRINT ''
OPEN NM
FETCH NEXT FROM NM INTO @DN
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Top Object for Database ' + @DN
SET @CMD1='select top 10  CONVERT(VARCHAR(35), name) "Tablename", (convert(bigint,sum(reserved))*8192/1024/1024) "Reserved Size(MB)", sum(rows) "Total Rows" from '+@DN+'..sysindexes   where indid in (0,1,255) group by  name order by 3 desc'
EXEC SP_EXECUTESQL @CMD1
FETCH NEXT FROM NM INTO @DN
END
CLOSE NM
DEALLOCATE NM
GO

'SPACE DETAILS OF DATABASES' in Sql Server

PRINT '=========================='
PRINT 'SPACE DETAILS OF DATABASES'
PRINT '=========================='
EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

PRINT '=========================='
PRINT '     DATABASE DETAILS     '
PRINT '=========================='
SET NOCOUNT ON
DECLARE NM CURSOR LOCAL STATIC FOR
SELECT NAME FROM SYSDATABASES
DECLARE @DN VARCHAR(50)
DECLARE @CMD1 NVARCHAR(4000)
PRINT ''
OPEN NM
FETCH NEXT FROM NM INTO @DN
WHILE @@FETCH_STATUS=0
BEGIN
print 'DB Details ' + @DN
set @CMD1='select CONVERT(VARCHAR(35), a.name)Name,CONVERT(VARCHAR(8),filegroup_name(a.groupid)),(a.size * 8)total_size ,b.spaceused from '+@DN+'..sysfiles a, ( select groupid, sum(reserved) spaceused from '+@DN+'..sysindexes where indid in (0,1,255) group by groupid ) b   where a.groupid = b.groupid'
EXEC SP_EXECUTESQL @CMD1
FETCH NEXT FROM NM INTO @DN
END
CLOSE NM
DEALLOCATE NM
GO

Sql Server Database Backup types :

Sql Server Database Backup types :

Sql Server Has mainly Four Types Of Data Backups :

(1). Full Backup.

(2).Differential Backup.

(3). File and File group Backup.

(4).Log File Backup.

(1)     Full Backup:- includes the all Data files and log files in a database. it is a complete set of data need to restore and recover the database in consist stat .

                       Or

 you can say that full backup is a complete set of file and files groups is equal to that full backup.

     

 

       (2). Differential Backup :- includes all the changes made since last full backup .differential backup in not a stand alone backup. when ever you required to restore or recover that data from differential backup then first you need to restore the full backup .if you have been taken multiple differential backups then you need to restore only latest one backup except that all backup.

(3)File or File groups backup :- Includes the backup of particular file and file groups that specified. Most of time you take currently used file and file groups backup that as  default file groups with a particular database .this type of backup you take only when database size in tera bytes

         (4)Log File Backup:-includes all the data recorded  in log file after taken the full backup or last log backup.

When you take log backup first time then it take backup of all log records that changed after that full backup.

If you take backup second or third time and so on then it start always last log backup.

Sql Server DBA Interview questions

Please complete all below questions series after that attend any interview for Sql DBA

1).what is the default Recovery model of a newly  created database ?

2).How many types of recovery model in Sql Server ?

3.)How many system databases in Sql Server 2005 ?

4). How you can say that Tempdb Database is different from other system and user databases ?

5).What is different between simple and full recovery model ?

6.)How to create a Database with 1 mdf,1ndf and 1 log file by T-Sql syntax ?

7).How to Add one more log file into a database by T-Sql syntax ?

8).How to add a file group to a Database  by T-Sql syntax?

9).How to add a log file with a test file group  by T-Sql syntax ?

10).How to find out database size , physical file location and database status as well ?

11) How to rename a Database by using T-Sql syntax?

12)How many types of  backup in sql Server 2008/2005/2000 ?

13) What is the usage of mssqlsystemresource database in sql server ?

14) How many types of challenges you face in your project ?

15 ) what is the different between differential and log backup ?

16) How to shrink the log file of  database ?

17) what is the different between shrink commend with truncate_only or no_truncate  clause ?

18) backup log DB name with truncate_only  is taking any backup or not if not ? why ?

19 ) How to take filegroup file backup of database ?

20) how to perform point in time recovery ?

21) How many types of replication in sql server 2008/2005/2000 ?

22) Which backup strategy (policy ) implement in our current project ?

23)How you configured Log Shipping or Database mirroring in current project if yes then please justify and how many types of issues you faced regarding log shipping and Database mirroring ?

24 ) Have u configured clustering on current project if yes then what king of clustering you configured and how ?

25) Tell me about your daily , weekly and monthly activities ?

26 ) How you enable log file in memory  for sql server ?

27) how to find out blocking process and deadlock on sql server ?

28 )How to move Master database from one location to another location ?

29)How to restore master database backup on sql server ?

30)what is different between windows authentication and sql server authentication ?which one better then other ? why ?

31)How to find out currently running process on sql server and which one process taking long time to execute ?

32)Which process or job taking high C.P.U. Utilization ?

33)IF Sql server going hang again and again what are steps to follow  to resolved problem ?

Attach Database with MDF file only

Use master
go
create database test on
(name=logicalfilename,
 filename='physical path of mdf file ')
for attach_rebuild_log
go

Above commend create one log file for this test DB and location for physical file as same as mdf file .
this commend is very useful if you lost the ldf file of any user database.

How to attach and deattached database in sql server by T-sql ?

Please use the below query :

For Attach :

USE
GO [master]CREATE DATABASE [abc] ON (
( FILENAME = N'D:\sql_data\abc.mdf' ), FILENAME = N'D:\sql_data\abc_log.ldf' )
GO

For Deattach :

USE
GO [master]EXEC master.dbo.sp_detach_db @dbname = N'abc', @keepfulltextindexfile=N'true'GO

After that refersh the database tab and check your currently attached data base.FOR ATTACH

How to move database physical_files from one drive to another drive (location

Please follow the below steps :

1)  Alter database abc (file=logical file name for data file ,
filename='new location or path');
Alter database abc (file=logical file name for log file ,
filename='new location or path');
for example
Alter database abc (file=abc,
filename='d:\abc.mdf');
Alter database abc (file=abclog,
filename='e:\abc_log.ldf');

2) Stop the sql server services.

3) Move the physical files (.MDF and LDF) from old location to new location .

4).Restart the Sql Server Services .

5) Veryify new location by using below command 

select * from sys.master_files ;

SQL EXERCISE

Retrieve the required information using SQL language.

Part I. Give a database schema for a library management system as the following picture.

1. How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
2. How many copies of the book titled The Lost Tribe are owned by each library branch?
3. Retrieve the names of all borrowers who do not have any books checked out .
4. For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
6. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
7. For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central"

Part II Give a database schema of a company as the following picture.

8. Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
9. For each project, list the project name and the total hours per week (by all employees) spent on that project.
10. Retrieve the names of employees who work on every project.
11. Retrieve the names of employees who do not work on any project.
12. Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston.
13. List the last names of department managers who have no dependents.
14. Find details of those employees whose salary is > the average salary for all employees. Output salary in descending order.
15. Find details of those employees whose salary is > the average salary for all employees in his/her department. Output salary in ascending order.

CREATE TABLE Manufacturers (

        Code INTEGER PRIMARY KEY NOT NULL,

        Name TEXT NOT NULL 

);

CREATE TABLE Products (

        Code INTEGER PRIMARY KEY NOT NULL,

        Name TEXT NOT NULL ,

        Price REAL NOT NULL ,

        Manufacturer INTEGER NOT NULL 

                CONSTRAINT fk_Manufacturers_Code REFERENCES MANUFACTURERS(Code)

);

1.Select the average price of each manufacturer's products, showing the manufacturer's name.
2. Select the name of each manufacturer along with the name and price of its most expensive product.
3. Select the name and price of all products with a price larger than or equal to $180, and sort first by price (in descending order), and then by name (in ascending order).
4. Select the names of manufacturer whose products have an average price larger than or equal to $150.

Answer:

-         Should not refer to the answer before trying to write down your solutions

-         The answer for each question is only one (or two) of the many other solutions

Solution 1:

SELECT bc.No_Of_Copies

FROM BOOK b, BOOK_COPIES bc, LIBRARY_BRANCH bl

WHERE         b.BookId = bc.BookId AND

                        bc.BranchId = bl.BranchId AND

Title='The Lost Tribe' AND BranchName='Sharpstown';

Solution 2:

SELECT No_Of_Copies

FROM ((BOOK NATURAL JOIN BOOK_COPIES ) NATURAL JOIN            LIBRARY_BRANCH )

WHERE Title='The Lost Tribe' AND BranchName='Sharpstown';

2. SELECT BranchName, No_Of_Copies

FROM ((BOOK NATURAL JOIN BOOK_COPIES ) NATURAL JOIN            LIBRARY_BRANCH )

WHERE Title='The Lost Tribe';

Solution 1:

SELECT         Name

FROM                        BORROWER B

WHERE         CardNo NOT IN (SELECT CardNo

                                                      FROM BOOK_LOANS );

Solution 2:

SELECT         Name

FROM                        BORROWER B

WHERE         NOT EXISTS (SELECT *

                                          FROM BOOK_LOANS L

                                          WHERE B.CardNo = L.CardNo );

4. SELECT B.Title, R.Name, R.Address

FROM BOOK B, BORROWER R, BOOK_LOANS BL, LIBRARY_BRANCH LB

WHERE LB.BranchName='Sharpstown' AND LB.BranchId=BL.BranchId AND

BL.DueDate='today' AND BL.CardNo=R.CardNo AND BL.BookId=B.BookId

5. SELECT         L.BranchName, COUNT(*)

FROM                        LIBRARY_BRANCH L, BOOK_LOANS BL

WHERE         BL.BranchId = L.BranchId

GROUP BY    L.BranchName;

6. SELECT         B.Name, B.Address, COUNT(*)

FROM                        BORROWER B, BOOK_LOANS L

WHERE         B.CardNo = L.CardNo

GROUP BY    B.CardNo, B.Name, B.Address

HAVING        COUNT(*) > 5;

Solution 1:

SELECT         Title, No_Of_Copies

FROM   (((BOOK_AUTHORS NATURAL JOIN BOOK) NATURAL JOIN BOOK_COPIES) NATURAL JOIN LIBRARY_BRANCH)WHERE Author_Name='Stephen King' AND BranchName='Central';

Solution 2: Student should write the another solution not using the natural join.

8. SELECT         LNAME, FNAME

FROM            EMPLOYEE, WORKS_ON, PROJECT

WHERE         DNO=5 AND SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX' AND HOURS>10;

9. SELECT                     PNAME, SUM (HOURS)

FROM                        PROJECT, WORKS_ON

WHERE                     PNUMBER=PNO

GROUP BY    PNAME;

Note:  The Group By clause should be replaced as GROUP BY          (PNUMBER, PNAME) since there   may be some projects have the same name.

Solution 1:

SELECT         E.LNAME, E.FNAME

FROM            EMPLOYEE E

WHERE         NOT EXISTS (SELECT PNUMBER

                              FROM            PROJECT

                              WHERE         PNUMBER NOT IN (SELECT PNO

                                                                  FROM WORKS_ON

                                                                  WHERE ESSN=E.SSN ) );

Solution 2:

SELECT         LNAME, FNAME

FROM            EMPLOYEE

WHERE         NOT EXISTS (SELECT PNUMBER

                              FROM            PROJECT

                              WHERE         NOT EXISTS (SELECT *

                                                      FROM WORKS_ON

                                                      WHERE PNUMBER=PNO AND ESSN=SSN ) );

Solution 1:

SELECT                     LNAME, FNAME

FROM                        EMPLOYEE

WHERE                     SSN NOT IN ( SELECT ESSN

                                                    FROM WORKS_ON);

                                               

Solution 2:

SELECT                     LNAME, FNAME

FROM                        EMPLOYEE

WHERE                     NOT EXISTS ( SELECT *

                                                    FROM WORKS_ON

                                                    WHERE ESSN=SSN );

Solution 1:

SELECT         LNAME, FNAME, ADDRESS

FROM            EMPLOYEE

WHERE         EXISTS ( SELECT *

                              FROM WORKS_ON W, PROJECT P, DEPT_LOCATIONS DL

                              WHERE         W.PNO = P.PNUMBER AND

                                                      P.DNUM = DL.DNUM   AND

                                                      DL.DLOCATION <> ‘Houston’);

Solution 2:

SELECT         LNAME, FNAME, ADDRESS

FROM            EMPLOYEE

WHERE         EXISTS ( SELECT *

                  FROM WORKS_ON, PROJECT

                  WHERE SSN=ESSN AND PNO=PNUMBER AND PLOCATION='Houston' )

                  AND

                  NOT EXISTS ( SELECT *

                  FROM DEPT_LOCATIONS

                  WHERE DNO=DNUMBER AND DLOCATION='Houston' );

Solution 1:

SELECT         E.LNAME, E.FNAME

FROM            EMPLOYEE E, DEPARTMENT D

WHERE         E.SSN = D.MRGSSN AND

NOT EXISTS ( SELECT DEPENDENT_NAME

                              FROM DEPENDENT

                              WHERE ESSN=E.SSN )

                                   

Solution 2:

SELECT         LNAME, FNAME

FROM                        EMPLOYEE

WHERE         EXISTS ( SELECT *

                              FROM DEPARTMENT

                              WHERE SSN=MGRSSN )

                              AND

                              NOT EXISTS ( SELECT *

                              FROM DEPENDENT

                              WHERE SSN=ESSN );

14. SELECT         *

FROM            Employee

WHERE         Salary > (SELECT AVG (Salary)

                                    FROM Employee )

ORDER BY Salary DESC;

Solution 1:

SELECT         E.*

FROM    EMPLOYEE E, (SELECT DNO, AVERAGE(SALARY) AS LTB

    FROM EMPLOYEE

    GROUP BY DNO) AS A

WHERE         E.DNO = A.DNO AND

                        E.SALARY > LTB;

                                   

Solution 2:

SELECT         E.*

FROM    EMPLOYEE E

WHERE   E.SALARY  > (SELECT AVERAGE(SALARY)

   FROM EMPLOYEE

   WHERE DNO = E.DNO);

            Note: if we want to display the average of the salary we should employ the solution 1 and put the “field name” LT

B in the select clause.