Transcript Database-lecture 10

Using sub query
Sub query

A query inside another query
Examples of Subqueries
Example 1
Retrieve the name of all employees who have dependents
Example 1(continued)
In this example: the sub query is used in the where clause.
The example goal: to retrieve the ssn for the employees who have at least
one dependent.
Example 2
Retrieve the name of all employees who works on two projects
Example 2
In this example: the sub query is used in the having clause
The example goal: to retrieve the number of projects for each employee who
works on at least one project in the works_on table.
Example 3
Retrieve the name of all employees with the department name each
employee works on.
Example 3
In this example: the sub query is used in the select clause.
The example goal: to retrieve the name of the department that each employee
works on.
Example 4
Retrieve all employees who works on all projects
Example 5
Update the location of all projects controlled by Headquarter department to
be in New york
Before update
After update
Example 6
Delete all employees in works_on table who works on all the projects located
in Belliare
Before delete
After delete
Backup and restore a database
Backup a database
Step 1
Backup a database
Step 2: click ok to backup
The
location
for the
backed up
database
Restore a database
Step 1:
Restore a database
Step 2: choose from device
Restore a database
Step 3: add the database backup you want to restore
Restore a database
Step 4: choose the location of the database backup you want to restore.
Restore a database
Step 5: Now press OK for the last two interfaces
Check
the check
box
Choose your
database
Restore a database
Step 6: add the red colored text before the database location to correct the
location of the database
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\clinic.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\clinic_1.LDF
Restore a database
Step 7: Now your database created successfully
The restored
database