SQL Training - 1

Syllabus of SQL Training

1. Structured Query language (SQL): A structured Query language is a computer language for a relational database system. Relational database system like MySQL oracle Sybase Informix uses SQL as standard database language for storing retrieving manipulating data and store in a relational database. Here are some SQL commands that are used for communication with databases like storing retrieving manipulating data.

• Data definition language used to create, drop, alter, and truncate in database.
• Data manipulation language used to insert, update and delete data in the database.
• Data control language used for Revoke and grant data.
• Transaction control language used for commit, and rollback data.
• Data Query language used for select data.

2. Transact structured Query language (T-SQL): TSQL stands for Transact structured Query language which is a Microsoft tool and extension of SQL language. TSQL is mainly used for writing an entire program of block function procedure that defines how things need to be complete and creating an application in which each application sends transact query over SQL server and there is no interaction with the database. It executes as a whole block with the extension of SQL language. There are different types of T-SQL functions are used in T-SQL :

• Scalar function
• Ranking function
• Aggregate function
• Rowset function

CREATE TABLE regions ( region_id INT IDENTITY(1,1) PRIMARY KEY, region_name VARCHAR (25) DEFAULT NULL ); --DELETE CASCADE: When we create a foreign key using this option, it deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key. --UPDATE CASCADE: When we create a foreign key using PDATE CASCADE the referencing rows are updated in the child table when the referenced row is updated in the parent table which has a primary key. CREATE TABLE countries ( country_id CHAR (2) PRIMARY KEY, country_name VARCHAR (40) DEFAULT NULL, region_id INT NOT NULL, FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE locations( location_id INT IDENTITY(1,1) PRIMARY KEY, street_address VARCHAR (40) DEFAULT NULL, postal_code VARCHAR (12) DEFAULT NULL, city VARCHAR (30) NOT NULL, state_province VARCHAR (25) DEFAULT NULL, country_id CHAR (2) NOT NULL, FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE jobs ( job_id INT IDENTITY(1,1) PRIMARY KEY, job_title VARCHAR (35) NOT NULL, min_salary DECIMAL (8, 2) DEFAULT NULL, max_salary DECIMAL (8, 2) DEFAULT NULL ); CREATE TABLE departments ( department_id INT IDENTITY(1,1) PRIMARY KEY, department_name VARCHAR (30) NOT NULL, location_id INT DEFAULT NULL, FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE employees ( employee_id INT IDENTITY(1,1) PRIMARY KEY, first_name VARCHAR (20) DEFAULT NULL, last_name VARCHAR (25) NOT NULL, email VARCHAR (100) NOT NULL, phone_number VARCHAR (20) DEFAULT NULL, hire_date DATE NOT NULL, job_id INT NOT NULL, salary DECIMAL (8, 2) NOT NULL, manager_id INT DEFAULT NULL, department_id INT DEFAULT NULL, FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE, ); /*Data for the table regions */ INSERT INTO regions(region_name) VALUES ('Europe'); INSERT INTO regions(region_name) VALUES ('Americas'); INSERT INTO regions(region_name) VALUES ('Asia'); INSERT INTO regions(region_name) VALUES ('Middle East and Africa'); /*Data for the table countries */ INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','Belgium',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','Brazil',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','Canada',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','Switzerland',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','China',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','Germany',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','Denmark',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','Egypt',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','France',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','HongKong',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','Israel',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','India',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','Italy',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','Japan',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','US',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','UK',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','DE',4); select * from countries select * from locations /*Data for the table locations */ INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2014 Jabberwocky Rd','26192','Southlake','Texas','US'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2011 Interiors Blvd','99236','South San Francisco','California','US'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2004 Charade Rd','98199','Seattle','Washington','US'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2004 Charade Rd','98199','Seattle','Washington','US'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('8204 Arthur St',NULL,'London',NULL,'UK'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'); INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'); /*Data for the table jobs */ select * from jobs INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Public Accountant',4200.00,9000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Accounting Manager',8200.00,16000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Administration Assistant',3000.00,6000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('President',20000.00,40000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Administration Vice President',15000.00,30000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Accountant',4200.00,9000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Finance Manager',8200.00,16000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Human Resources Representative',4000.00,9000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Programmer',4000.00,10000.00); INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Marketing Manager',9000.00,15000.00); /*Data for the table departments */ select * from locations INSERT INTO departments(department_name,location_id) VALUES ('Administration',10); INSERT INTO departments(department_name,location_id) VALUES ('Marketing',11); INSERT INTO departments(department_name,location_id) VALUES ('Purchasing',12); INSERT INTO departments(department_name,location_id) VALUES ('Human Resources',15); INSERT INTO departments(department_name,location_id) VALUES ('Shipping',11); INSERT INTO departments(department_name,location_id) VALUES ('IT',11); INSERT INTO departments(department_name,location_id) VALUES ('Public Relations',12); INSERT INTO departments(department_name,location_id) VALUES ('Sales',4); INSERT INTO departments(department_name,location_id) VALUES ('Executive',7); INSERT INTO departments(department_name,location_id) VALUES ('Finance',9); INSERT INTO departments(department_name,location_id) VALUES ('Accounting',9); select * from departments select * from employees /*Data for the table employees */ INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Steven','King','steven.king@sqltutorial.org','515.123.4567','1987-06-17',4,24000.00,NULL,8); INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Neena','Kochhar','neena.kochhar@sqltutorial.org','515.123.4568','1989-09-21',5,17000.00,100,9); INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Lex','De Haan','lex.de haan@sqltutorial.org','515.123.4569','1993-01-13',5,17000.00,100,8); INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Alexander','Hunold','alexander.hunold@sqltutorial.org','590.423.4567','1990-01-03',9,9000.00,102,9); INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Bruce','Ernst','bruce.ernst@sqltutorial.org','590.423.4568','1991-05-21',9,6000.00,103,10); INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('David','Austin','david.austin@sqltutorial.org','590.423.4569','1997-06-25',9,4800.00,103,11);

-------------------------------

Describe the data set used by the course 

Log on to the database using SQL Developer environment 

Save queries to files and use script files in SQL Developer 

Retrieve Data using the SQL SELECT Statement 

List the capabilities of SQL SELECT statements 

Generate a report of data from the output of a basic SELECT statement Select All Columns 

Select Specific Columns 

Use Column Heading Defaults 

Use Arithmetic Operators 

Understand Operator Precedence 

Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data 

Write queries that contain a WHERE clause to limit the output retrieved List the comparison operators and logical operators that are used in a WHERE clause Describe the rules of precedence for comparison and logical operators 

Use character string literals in the WHERE clause 

Write queries that contain an ORDER BY clause to sort the output of a SELECT statement Sort output in descending and ascending order 

Usage of Single-Row Functions to Customize Output 

Describe the differences between single row and multiple row functions Manipulate strings with character function in the SELECT and WHERE clauses Manipulate numbers with the ROUND, TRUNC, and MOD functions 

Perform arithmetic with date data 

Manipulate dates with the DATE functions 

Invoke Conversion Functions and Conditional Expressions 

Describe implicit and explicit data type conversion 

Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions Nest multiple functions

Apply the NVL, NULLIF, and COALESCE functions to data 

Use conditional IF THEN ELSE logic in a SELECT statement 

Aggregate Data Using the Group Functions 

Use the aggregation functions in SELECT statements to produce meaningful reports Divide the data into groups by using the GROUP BY clause 

Exclude groups of date by using the HAVING clause 

Display Data From Multiple Tables Using Joins 

Write SELECT statements to access data from more than one table View data that generally does not meet a join condition by using outer joins Join a table by using a self-join 

Use Sub-Queries to Solve Queries 

Describe the types of problem that sub-queries can solve 

Define sub-queries 

List the types of sub-queries 

Write single-row and multiple-row sub-queries 

The SET Operators

Describe the SET operators 

Use a SET operator to combine multiple queries into a single query Control the order of rows returned 

Data Manipulation Statements 

Describe each DML statement 

Insert rows into a table 

Change rows in a table by the UPDATE statement 

Delete rows from a table with the DELETE statement 

Save and discard changes with the COMMIT and ROLLBACK statements Explain read consistency 

Use of DDL Statements to Create and Manage Tables 

Categorize the main database objects 

Review the table structure 

List the data types available for columns 

Create a simple table 

Decipher how constraints can be created at table creation 

Describe how schema objects work

Other Schema Objects 

Create a simple and complex view 

Retrieve data from views 

Create, maintain, and use sequences 

Create and maintain indexes 

Create private and public synonyms 

Control User Access 

Differentiate system privileges from object privileges Create Users 

Grant System Privileges 

Create and Grant Privileges to a Role 

Change Your Password 

Grant Object Privileges 

How to pass on privileges? 

Revoke Object Privileges 

Management of Schema Objects 

Add, Modify and Drop a Column

Add, Drop and Defer a Constraint 

How to enable and Disable a Constraint? 

Create and Remove Indexes 

Create a Function-Based Index 

Perform Flashback Operations 

Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP Query External Tables 

Manage Objects with Data Dictionary Views 

Explain the data dictionary 

Use the Dictionary Views 

USER_OBJECTS and ALL_OBJECTS Views 

Table and Column Information 

Query the dictionary views for constraint information 

Query the dictionary views for view, sequence, index, and synonym information Add a comment to a table 

Query the dictionary views for comment information 

Manipulate Large Data Sets

Use Subqueries to Manipulate Data 

Retrieve Data Using a Subquery as Source 

Insert Using a Subquery as a Target 

Usage of the WITH CHECK OPTION Keyword on DML Statements List the types of Multitable INSERT Statements 

Use Multitable INSERT Statements 

Merge rows in a table 

Track Changes in Data over a period of time 

Data Management in Different Time Zones 

Time Zones 

CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP þÿCompare Date and Time in a Session  s Time Zone DBTIMEZONE and SESSIONTIMEZONE 

Difference between DATE and TIMESTAMP 

INTERVAL Data Types 

Use EXTRACT, TZ_OFFSET, and FROM_TZ 

Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries 

Multiple-Column Subqueries 

Pairwise and Nonpairwise Comparison 

Scalar Subquery Expressions 

Solve problems with Correlated Subqueries 

Update and Delete Rows Using Correlated Subqueries The EXISTS and NOT EXISTS operators 

Invoke the WITH clause 

The Recursive WITH clause 

Regular Expression Support 

Use the Regular Expressions Functions and Conditions in SQL Use Meta Characters with Regular Expressions 

Perform a Basic Search using the REGEXP_LIKE function Find patterns using the REGEXP_INSTR function Extract Substrings using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE function Usage of Sub-Expressions with Regular Expression Support

Implement the REGEXP_COUNT function