MOTIVATION

instructions
You will be shown several
pictures of computer parts.

Write the name of each part down on a

Give yourself one point for each correct

The one who gets the most

Have Fun!
advanced
sql
Structured Query
Language
is used to perform operations on the
records stored in the database, such as
updating records, inserting records,
deleting records, creating and modifying
database tables, views, etc.
Advanced SQL
include transactions, normal forms, main
and foreign keys, hierarchical queries,
triggers, indices, stored procedures, and
much more.
SQL Syntax
is a unique set of rules and
guidelines.
SQL Operator Symbols Operators
** Exponentiation operator
+, - Identity operator, Negation operator
*, / Multiplication operator, Division
operator
+, -, || Addition (plus) operator, subtraction
(minus) operator, String Concatenation
operator
=, !=, <, >, <=, >=, IS NULL, LIKE, Comparison Operators
BETWEEN, IN
NOT Logical negation operator
&& or AND Conjunction operator
OR Inclusion operator
FUNDAMENT
AL
CONCEPTS
EXAMPLE:
Window Functions
SELECT month, sales, SUM(sales)
OVER (ORDER BY month)
use to perform AS on a
calculations
"window" of data that is defined
cumulative_salesFROM sales_data; by a
particular subset of rows in a table.
EXAMPLE:
Common Table
WITH product_customers AS (
SELECT name, age
Expressions
FROM customer_data
WHERE product = 'books'
using this you can create a temporary
) named result set that you can use in
SELECT AVG(age)
subsequent queries AS avg_age
within the same
FROM product_customers;
session.
Create Two Tables
is particularly useful when you need to
combine data from different tables to
answer a specific query.
JOIN
is used when we have to obtain results by
combining two or more tables based on
common property.
Syntax
INNER JOIN
SELECT ProductID, ProductName,
CategoryName
FROMreturns
Productsrecords that have matching
values in both table.
INNER JOIN Categories ON Products.
CategoryID = Categories.CategoryID;
Syntax
LEFT JOIN
SELECT column_name(s)
FROM table1
returns all records from the left table,
LEFT JOIN table2
and the matched records from the right
ON table.
table1.column_name = table2.colu
mn_name;
Syntax
RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHTreturns
JOINall table2
records from the right table,
and the matched records from
ON table1.column_name = table2.colu the left
table.
mn_name;
Syntax
OUTER JOIN
SELECT column_name(s)
FROM table1
FULL OUTER
returns allJOIN table2
records when there is a
ON match
table1.column_name = table2.colu
in either left or right table.
mn_name
WHERE condition;
Syntax
UNION
SELECT column_name(s) FROM table1
UNION
used to combine the result-set of two or
SELECT column_name(s)
more SELECT statements. FROM table2
;
Syntax
GROUP BY
SELECT column_name(s)
FROM table_name
statement is often used with aggregate functions
WHERE condition
(COUNT(), MAX(), MIN(), SUM(), AVG())
GROUP BY column_name(s)
to group the result-set by one or more
ORDER
columns. BY column_name(s);
Syntax
SQL HAVING
SELECT column_name(s)
FROM table_name
was added to SQL because
WHERE condition
the WHERE keyword cannot be used with
GROUP BY column_name(s)
aggregate functions.
HAVING condition
ORDER BY column_name(s);
Syntax
EXISTS
SELECT column_name(s)
FROM table_name
operator is used to test for the existence
WHERE EXISTS
of any record in a subquery.
(SELECT column_name FROM table_n
ame WHERE condition);
Syntax
SELECT INTO
SELECT *
INTO newtable [IN externaldb]
statement copies data from one table
FROM oldtable
into a new table.
WHERE condition;
Syntax
Insert Into Select
INSERT INTO table2
SELECT * FROM table1
copies data from one table and inserts it
WHERE condition;
into another table.
How to Export Ms Access
Database to MySQL?
PROCESS INVOLVED
1. External Table
2. Data Source
3. From Database
4. From Sql Server
5. Link to the data source by creating a linked table
6. Machine Data Source
7. Choose Data Source
8. Select Link Table
Create an SQL database using
Microsoft Access, and link it to
MySQL within a 30-minute
timeframe.
Criteria Excellent (4) Good (3) Satisfactory (2) Needs
Improvement
(1)
Understanding Identifies and Demonstrates Demonstrates Demonstrates
of Concepts demonstrates good satisfactory limited
exceptional understanding of understanding of understanding of
understanding SQL database. SQL database. SQL database.
ofSQL database.
Clarity and Creates a database Creates a database Creates a database Creates a database
Completeness that is that is clear, well- that is generally that is unclear and
exceptionally organized, and clear and well- poorly organized.
clear, well- easy to organized, with
organized, and understand. some areas for
easy to improvement.
understand.
Criteria Excellent (4) Good (3) Satisfactory (2) Needs
Improvement
(1)
Creativity and Demonstrates Demonstrates Demonstrates Demonstrates
Originality exceptional good creativity in satisfactory limited creativity
creativity in designing the creativity in in designing the
designing the database. designing the database.
database. Goes database.
beyond basic
requirements to
create an original
design.
Teamwork Collaborates Collaborates Collaborates Collaborates
exceptionally effectively with satisfactorily with ineffectively with
effectively with team members. team members. team members.
team members.
What is advanced SQL, its
purpose, and its significance?
What are the four types of join?
Explain each one.
What are the fundamental concepts
of advanced SQL?
1. You're tasked with generating comprehensive
reports for a university library management
system, including how many books students
borrow each month and tracking which books
they borrow. What database skill is crucial for
handling these tasks efficiently?
A.Advance SQL
B.Basic SQL
C.Queries
D.Subqueries
2. You're managing a university library and have two
separate databases: one containing student
information and another containing details of library
resources. What SQL operation would you use to
merge the student data with the library resource
information, ensuring that all students are considered
and have access to library resources?
A.Group by
B.Join
C.Union
D.Exists
3. You're managing a university library, and your goal
is to compile a complete list of books available for
student case study projects. To achieve this, you must
merge separate datasets containing information on
book availability. What SQL operation would
effectively combine these datasets?
A.Union
B.Group by
C.Inner Join
D.Join
4. What SQL feature allows you to perform
calculations of rows in a result set, without grouping
the rows into a single output row?
A.Aggregate functions
B.Subqueries
C.Window Functions
D.Join
5. You're learning SQL and attempting to write a
simple query to retrieve students' information from a
university database. What term describes the specific
rules and structure that must be followed when writing
SQL queries?
A.Syntax
B.Aggregate
C.Subquery
D.Join
KEY TO CORRECTION
1. A.
2. B.
3. A.
4. C.
5. A.
ASSIGNMENT
Find a partner and design a database for a
library management system using
Microsoft Access, and link it to MySQL.
ADVANCE ASSIGNMENT
1. What is database design tool?
2. What software is used for database
design?
REFERENCE
https://miro.com/diagramming/database-
design/
Thank
You!