SQL

Basic Tasks

• Retrieve data from a database
• Update data on a database
• Insert data on a database
• Delete data from a database
• In one word - Language for relational DATABASE MANAGEMENT

Types of SQL

• ORACLE - Large and multi-users DB management system Relational Database Management System (RDBMS), Owned By Oracle
• MySQL - Runs as a server providing multi users access to a number of DB (RDBMS), Open Source

• POSTGRESQL - Object Relational Database Management System (ORDBMS) [It allows you to add custom functions developed using different programming languages such as C/C++, Java]

• SQLite & many more

MYSQL VS ORACLE

1.Oracle is way more powerful than MySQL

2.Oracle supports distributed databases while MySQL doesn’t

3.Oracle supports save points while MySQL doesn’t

4.Oracle allows programs within the database while MySQL doesn’t

5.Oracle is suited for enterprise deployments while MySQL is suited for small to medium scale

6.Oracle requires that you pay a licensing fee while MySQL doesn’t

Data Types of SQL

SQL Command Types

• DML - Data Manipulation Language
• DDL - Data Definition Language
• DCL - Data Control Language

DML - Data Manipulation Language

Select

Insert

Update

Delete

Merge

*in DML it needs COMMIT command to save in the database, otherwise its not saved

DDL - Data Definition Language

** DR CAT

• Drop
• Rename
• Create
• Alter
• Truncate

DCL - Data Control Language

• Revoke - Takes Back Permission From User
• Grant - Gives User Access Privileges to database

The Database Model

SQL Query

Basic form:

SELECT attributes

FROM relations

WHERE conditions

UPPER = where you write the all content such as name in capital letter

Lower= where you write the all content such as name in smaller letter

QUESTION:

GET INFORMATION OF THE EMPLOYEE ‘ALANA’

SELECT * FROM EMPLOYEES

WHERE

UPPER (FIRST_NAME)= 'ALANA'

lower(first_name)=‘alana’

'%'

When we need to find information

QUE: give all employees first_name with p

Select * from employees

Where

UPPER (first_name) like ‘P%’

Group Function

1. MAX

2. AVG

3. MIN

4. COUNT

5. SUM

Saving on SQL

To save your work from sql to doc file

Write spool c:\nameofthedocument.doc beginning of your query

Write your query

Select * from employees

And spool off (end of your query)