unit5 joins subq views tran
TRANSCRIPT
-
8/3/2019 Unit5 Joins SubQ Views Tran
1/49
Unit 5: Joins, Sub QueriesViews and Transactions
Pratian Technologies (India) Pvt. Ltd.www.pratian.com
-
8/3/2019 Unit5 Joins SubQ Views Tran
2/49
Overview
What are Joins?
Types of Joins
Cross join
Inner join Left join
Right Join
Unions
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
SQL Functions
String
Date
Numeric Summarized
-
8/3/2019 Unit5 Joins SubQ Views Tran
3/49
Overview
What are subqueries?
Types of subqueries
Normal
Correlated Predicates with subqueries
Restrictions with subqueries
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Views
Transactions
What are transactions
Transaction control
Commit
Rollback
-
8/3/2019 Unit5 Joins SubQ Views Tran
4/49
JOINS
JOIN is a query that combines data from more than onetable by means of a single statement
Joining is done in SQL by specifying the tables to bejoined in the FROM clause
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
os o n quer es con a n con ons acompare two columns, each from a different table. Sucha condition is called join condition
-
8/3/2019 Unit5 Joins SubQ Views Tran
5/49
CROSS JOIN
A cross-join between two tables takes the data from eachrow in table1 and joins it to the data from each row intable2.
Example To select students with their courseinformation
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
. , .
-> FROM Students s, Courses c
-
8/3/2019 Unit5 Joins SubQ Views Tran
6/49
CROSS JOIN Students Table
Courses Table
StudentId Name Age CourseId
1001 Krishna S 18 1
1002 Raghav V 19 2
CourseId Course
1 Basic SQL
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
2 Excel
Name Course
Krishna S Basic SQL
Krishna S Excel
Raghav V Basic SQL
Raghav V Excel
-
8/3/2019 Unit5 Joins SubQ Views Tran
7/49
EQUI JOIN
An equi join is a join condition containing an equalityoperator
Is also called inner join or simple join
In the equi-join the comparison we are making betweentwo columns is that they match the same value. We canuse this method to select certain fields from both tablesand onl the correct rows will be oined to ether.
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Example To select students with their courseinformation
SELECT s.StudentId, s.Name, c.CourseId, c.Course
FROM Students s, Courses cWHERE s.CourseId = c.CourseId
-
8/3/2019 Unit5 Joins SubQ Views Tran
8/49
EQUI JOIN Students Table
Courses Table
StudentId Name Age CourseId
1001 Krishna S 18 1
1002 Raghav V 19 2
CourseId Course
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Result
1 Basic SQL2 Excel
Name Course
Krishna S Basic SQL
Raghav V Excel
-
8/3/2019 Unit5 Joins SubQ Views Tran
9/49
SELF JOIN
A self join is a join of a table to itself
The table appears twice in the FROM clause and isfollowed by table aliases, that qualify table names in the
join condition The table rows are combined and the rows which satisfy
the condition are returned
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
names
SELECT s.Name as StudentName, s.Age, r.Name as ReferredBy
FROM Students s, Students r
WHERE s.ReferredById = r.StudentId
-
8/3/2019 Unit5 Joins SubQ Views Tran
10/49
SELF JOIN
Students
Result
StudentId Name Age CourseId ReferredById
1001 Krishna S 18 1 NULL
1002 Raghav V 19 2 1001
Student Name Age ReferredBy
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Raghav V 19 Krishna
-
8/3/2019 Unit5 Joins SubQ Views Tran
11/49
LEFT JOIN
A left join extends the result of a simple join
Returns all rows that satisfy the join conditions and thoserows from left table for which no rows from the other
satisfy the join condition Example: To get all students information, even those
without course information
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
. , . , . , .
FROM Students s
LEFT JOIN Courses c
ON s.CourseId = c.CourseId
-
8/3/2019 Unit5 Joins SubQ Views Tran
12/49
LEFT JOIN
Students
Courses
StudentId Name Age CourseId
1001 Krishna S 18 1
1002 Raghav V 19 NULL
1003 Veena N 18 2
CourseId Course
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Result
1 Basic SQL2 Excel
Name Age CourseId CourseKrishna S 18 1 Basic SQL
Raghav V 19 NULL NULL
Veena N 18 2 Excel
-
8/3/2019 Unit5 Joins SubQ Views Tran
13/49
RIGHT JOIN
A right join extends the result of a simple join
Returns all rows that satisfy the join conditions and thoserows from right table for which no rows from the other
satisfy the join condition Example: To get all courses with their students, even
those courses which doesnt have students assigned
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
. , . , . , .
FROM Students s
RIGHT JOIN Courses c
ON s.CourseId = c.CourseId
-
8/3/2019 Unit5 Joins SubQ Views Tran
14/49
RIGHT JOIN
Students
Courses
StudentId Name Age CourseId
1001 Krishna S 18 1
1002 Raghav V 19 NULL
1003 Veena N 18 1
CourseId Course
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Result
1 Basic SQL2 Excel
Name Age CourseId CourseKrishna S 18 1 Basic SQL
Veena N 18 1 Basic SQL
NULL NULL 2 Excel
-
8/3/2019 Unit5 Joins SubQ Views Tran
15/49
UNION
UNION is used to combine the result frommultiple SELECT statements into a single result set
There are few conditions to be kept in mind, when we use
UNION The number of columns in each SELECT statement has to be the
same
The data type of the columns in the column list of the SELECT
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
statement must be the same or at least convertible.
Syntax:
SELECT statement
UNION [DISTINCT | ALL]
SELECT statement
UNION [DISTINCT | ALL ]
-
8/3/2019 Unit5 Joins SubQ Views Tran
16/49
UNION
By default the UNION removes all duplicated rows fromthe result set even if you dont explicit use DISTINCT afterthe UNION keyword
If you use UNION ALL explicitly, the duplicated rows willremain in the result set
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
SELECT StudentId as Id, Name as Desc, Fees
FROM Students
WHERE Name LIKE %K
UNION
SELECT StudentId as Id, Name as Desc, Fees
FROM Students
WHERE Name LIKE %A
-
8/3/2019 Unit5 Joins SubQ Views Tran
17/49
SQL FUNCTIONS
Apart from aggregate functions there are some single rowfunctions which can be used in queries
They can be classified into the below mentioned
categories String
Numeric
Date
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Summarized
-
8/3/2019 Unit5 Joins SubQ Views Tran
18/49
SQL FUNCTIONS - String
UPPER (string) Converts all characters of a given string to upper case
SELECT UPPER(Name), JoinDate, Fees
FROM Students
LOWER(string)
Converts all characters of a given string to lower case
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
SELECT LOWER(Name), JoinDate, FeesFROM Students
REVERSE(string)
Returns the reverse of a string
Select REVERSE(ABCD)
Output - DCBA
-
8/3/2019 Unit5 Joins SubQ Views Tran
19/49
SQL FUNCTIONS - String
SUBSTRING(string, pos, len) orSUBSTRING(string FROM pos FOR len)
Extracts substring from a given string considering the position
and length provided Select SUBSTRING(Krishna, 2, 5)
Output rishn
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
CONCAT(string1, string2,.) Returns a string after concatenating all the strings provided
Arguments passed cannot be null
Select CONCAT(My, S, Q, L)
Output - MySQL
-
8/3/2019 Unit5 Joins SubQ Views Tran
20/49
SQL FUNCTIONS - String
LTRIM(string) Returns string with leading space characters removed
SELECT LTRIM( Krishna)
RTRIM(string)
Similar to LTRIM. Removes trailing space characters
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
CHAR_LENGTH(string) Returns the length of the string
Note: This is only a partial list
-
8/3/2019 Unit5 Joins SubQ Views Tran
21/49
SQL FUNCTIONS - Numeric
ROUND(x, d) or ROUND(x) Rounds the argument x to ddecimal places
Select ROUND(150.225, 2)
Output 150.22
ABS(x)
Returns the absolute values
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Select ABS(-32)
Output = 32
SQRT(x)
Returns the square root a non-negative number
Select SQRT(4)
Output = 2
Note: This is only a partial list.
-
8/3/2019 Unit5 Joins SubQ Views Tran
22/49
SQL FUNCTIONS - Date
DATE(expr) Returns the date from a given date and time value
Select DATE(2010-12-31 08:30:00)
Output 2010-12-31
CURDATE()
Returns the current date
Select CURDATE()
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Output 2010-11-01
DAY(date)
Returns day of the month, in the range 1 31
SELECT DAY(2010-11-01)
Output - 1
-
8/3/2019 Unit5 Joins SubQ Views Tran
23/49
SQL FUNCTIONS - Date
DATE_ADD(date, INTERVAL expr unit) Function performs date arithmetic
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECONDS;
Output - 2009-01-01 00:00:00
There are many unit values that can be specified
SECONDS
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
MINUTES HOURS
DAYS
WEEKS
MONTHS YEARS
etc..
Note: This is only a partial list
-
8/3/2019 Unit5 Joins SubQ Views Tran
24/49
SQL FUNCTIONS - Summarized
COUNT Produces the number of rows query has selected
AVG
Produces the average of all selected values of a given column
MAX
Produces the largest of all selected values of a given column
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Produces the smallest of all selected values of a given column
SUM
Produces the arithmetic sum of all selected values of a givencolumn
-
8/3/2019 Unit5 Joins SubQ Views Tran
25/49
SQL FUNCTIONS
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://dev.mysql.com/doc/refman/5.0/en/numeric-functions.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
-
8/3/2019 Unit5 Joins SubQ Views Tran
26/49
SUBQUERIES
A subquery is a SELECT statement within anotherstatement
Main advantages of subqueries are
Queries can be structured, so that it is possible to isolate eachpart of a statement
Provide alternate ways to perform operations that wouldotherwise require complex joins or unions
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Ex: SELECT * FROM Table1 WHERE Column1 = (SELECT Column1
FROM Table2)
A subquery can return a scalar (a single value), a single
row, a single column, or a table (one or more rows of oneor more columns)
-
8/3/2019 Unit5 Joins SubQ Views Tran
27/49
SUBQUERIES
Used in either SELECT, WHERE or FROM clauses of anSQL statement
In the WHERE clause subqueries can become a part ofthe following predicates
Comparison predicate
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
ANY or ALL predicate EXISTS predicate
Subqueries cannot have ORDER BY clause
-
8/3/2019 Unit5 Joins SubQ Views Tran
28/49
NORMAL SUBQUERIES
Does not need data from the outer query Subquery is evaluated only once
Subquery generates values that are tested in the
predicate of the outer query
To list all students who have enrolled for Basic SQL
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
SELECT Name, JoinDate
FROM Students
WHERE CourseId = (SELECT CourseId FROM Courses WHERECourse = BASIC SQL);
-
8/3/2019 Unit5 Joins SubQ Views Tran
29/49
NORMAL SUBQUERIES
Students
Courses
StudentId Name Age CourseId
1001 Krishna S 18 1
1002 Raghav V 19 2
1003 Veena N 18 1
CourseId Course
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Results
1 Basic SQL
2 Excel
Name Age CourseId CourseKrishna S 18 1 Basic SQL
Veena N 18 2 Basic SQL
-
8/3/2019 Unit5 Joins SubQ Views Tran
30/49
CORRELATED SUBQUERY
A correlated subquery uses any data from the FROMclause of the outer query
The subquery is evaluated for each row of the outer query
The subquery has to result in one value of the same data
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
ype as e e - an s e
To get list of students and the number of courses theyhave joined
SELECT s.Name, s.Age, (Select count(*) FROM StudentCourses scWHERE sc.StudentId = s.StudentId) as NoOfCourses
FROM Students s
-
8/3/2019 Unit5 Joins SubQ Views Tran
31/49
CORRELATED SUBQUERIES
Students
StudentCourses
StudentId Name Age CourseId
1001 Krishna S 18 1
1002 Raghav V 19 2
StudentId CourseId
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Results
1001 1
1001 2
1002 1
Name Age NoOfCourses
Krishna S 18 1
Raghav V 19 2
-
8/3/2019 Unit5 Joins SubQ Views Tran
32/49
PREDICATES WITH SUBQUERIES
Predicates that can be used with subqueries are IN
EXISTS
ALL
ANY/SOME
IN
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
SELECT DISTINCT(StudentId), Name, Fees
FROM Students
WHERE StudentId IN (Select st.StudentId FROMStudentCourses st)
-
8/3/2019 Unit5 Joins SubQ Views Tran
33/49
PREDICATES WITH SUBQUERIES
ALL SELECT Name, Fees
FROM Students
WHERE Fees > ALL (SELECT Fees FROM Students WHERE
CourseId = 1);
ANY
SELECT Name, Fees
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
FROM Students
WHERE Fess > ANY (SELECT Fees FROM Students WHERECourseId = 1);
EXISTS
SELECT s.Name, s.FeesFROM Students s
WHERE EXISTS (SELECT st.StudentId FROM StudentCoursesst WHERE st.StudentId = s.StudentId);
-
8/3/2019 Unit5 Joins SubQ Views Tran
34/49
SUBQUERIES
Can also be used in the FROM clause SELECT FROM (subquery) [AS] name
The [AS] name clause is mandatory as every table in FROMclause should have a name
Ex: Students who have paid more than Rs 1000 fees
SELECT StudentId, Name, Fees
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
FROM (Select StudentId, Name, Fees From Students WhereFess >= 1000) as StudentsFeesGreater
-
8/3/2019 Unit5 Joins SubQ Views Tran
35/49
SUBQUERIES - Restrictions
A subquery's outer statement can be any oneof: SELECT, INSERT, UPDATE, DELETE, SET, or DO.
In general, you cannot modify a table and select from thesame table in a subquery. For example, this limitationapplies to statements of the following forms
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
... ... ...
UPDATE t ... WHERE col = (SELECT ... FROM t ...); {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
There are few more restrictions, but above mentioned are the most
important
-
8/3/2019 Unit5 Joins SubQ Views Tran
36/49
QUESTION TIME
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
-
8/3/2019 Unit5 Joins SubQ Views Tran
37/49
VIEWS
Views are essentially saved SELECT queries that canthemselves be queried
It is stored as an object in the database
They are used to provide easier access to normalized
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
a a
Once you define a view then you can reference it likeany other table in a database
-
8/3/2019 Unit5 Joins SubQ Views Tran
38/49
VIEWS
Views have the following benefits: Security-
Views can be made accessible to users while the underlyingtables are not directly accessible. This allows the DBA to give
users only the data they need, while protecting other data in thesame table.
Simplicity-
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Views can be used to hide and reuse complex queries.
Column Name Simplification or Clarification -
Views can be used to provide aliases on column names to makethem more memorable and/or meaningful.
Stepping Stone- Views can provide a tiered approach in a "multi-level" query
systematically.
-
8/3/2019 Unit5 Joins SubQ Views Tran
39/49
VIEWS
Syntax CREATE VIEW [view_name] AS
SELECT statement goes here..
For Ex:
CREATE VIEW StudentCourses AS
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
SELECT s.StudentId, s.Name, s.Fees, c.CourseId, c.CourseFROM Students s, StudentCourses sc, Courses c
WHERE s.StudentId = sc.StudentId and sc.CourseId = c.CourseId
To query view SELECT * FROM StudentCourses
VIEWS
-
8/3/2019 Unit5 Joins SubQ Views Tran
40/49
VIEWS
Some restrictions imposed on views are given below : The view name must follow the rules for identifiers
The view name must not be the same as that of the base table
A view can be created if there is a SELECT permission on its
base table. A SELECT INTO statement cannot be used in view declaration
statement.
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
.
The CREATE VIEW statement cannot be combined with otherSQL statements in a single batch.
VIEWS
-
8/3/2019 Unit5 Joins SubQ Views Tran
41/49
VIEWS
ALTER VIEW ALTER VIEW [view_name] AS
SELECT statement that has been entered
For Ex:
ALTER VIEW StudentCourses AS
SELECT s.StudentId, s.Name, s.Fees, c.Course
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
FROM Students s, StudentCourses sc, Courses cWHERE s.StudentId = sc.StudentId
and sc.CourseId = c.CourseId
DROP VIEW DROP VIEW [view_name]
DROP VIEW StudentCourses
TRANSACTIONS
-
8/3/2019 Unit5 Joins SubQ Views Tran
42/49
TRANSACTIONS
A transaction is a unit of work that may contain of one ormore SQL statements
A transaction is called atomic as the databasemodifications brought about by the SQL statements thatconstitute a transaction can be
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Or, undone from the database
The changes made to a table using INSERT, UPDATE orDELETE statements are not permanent till a transaction is
marked complete
TRANSACTIONS
-
8/3/2019 Unit5 Joins SubQ Views Tran
43/49
TRANSACTIONS
A transaction will never be complete unless eachindividual operation within the group is successful
If any operation within the transaction fails, the entiretransaction will fail
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
ur ng a sess on, a ransac on eg ns w en e rs
command (DDL or DML) is encountered and ends whenone of the following occurs
A DDL is encountered
COMMIT/ROLLBACK statement is encountered
Logging off from the session
System failure
TRANSACTIONS Properties [ACID]
-
8/3/2019 Unit5 Joins SubQ Views Tran
44/49
TRANSACTIONS Properties [ACID]
Atomicity: ensures that all operations within the work unitare completed successfully; otherwise, the transaction isaborted at the point of failure, and previous operations arerolled back to their former state.
Consistency: ensures that the database properlychanges states upon a successfully committedtransaction.
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
Isolation: enables transactions to operate independentlyof and transparent to each other.
Durability: ensures that the result or effect of acommitted transaction persists in case of a system failure.
TRANSACTIONS COMMIT
-
8/3/2019 Unit5 Joins SubQ Views Tran
45/49
TRANSACTIONS - COMMIT
Committing a transaction makes permanent the changesresulting from all successful SQL statements in atransaction
Syntax: COMMIT;
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
op on s ava a e o execu e
automatically whenever an INSERT, UPDATE or DELETEstatement is executed
Syntax: SET AUTOCOMMIT = 1;
TRANSACTIONS ROLLBACK
-
8/3/2019 Unit5 Joins SubQ Views Tran
46/49
TRANSACTIONS - ROLLBACK
Changes made to the database without COMMIT may beabandoned using the ROLLBACK statement
When a transaction is rolled back, it is as if the transactionnever occurred
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
yn ax: ;
TRANSACTIONS
-
8/3/2019 Unit5 Joins SubQ Views Tran
47/49
TRANSACTIONS
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
TRANSACTIONS
-
8/3/2019 Unit5 Joins SubQ Views Tran
48/49
TRANSACTIONS
Syntax: BEGIN TRANSACTION
Insert into table1..
SAVEPOINT firststep
Update table2..
IF ERR = 1
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com
ROLLBACK firststep
COMMIT;
Question Time
-
8/3/2019 Unit5 Joins SubQ Views Tran
49/49
Please try to limit the questions to the topics discussed during the session. Thank you
.
Question Time
Basic SQLCopyright 2010 Pratian Technologieswww.pratian.com