SQL Tutorial, Introduction to SQL - PowerPoint PPT Presentation

View by Category
About This Presentation
Title:

SQL Tutorial, Introduction to SQL

Description:

SQL Tutorial, Introduction to SQL – PowerPoint PPT presentation

Number of Views:801

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Tutorial, Introduction to SQL


1
SQL
  • Structured Query Language
  • Path from Unorganized to Organized.

2
What is a Database
  • A database is an organized collection of data.
    The database management system(DBMS) is the
    system which acts as medium to carry data from
    database and user.
  • There are 4 types of DBMS

Hierarchical DBMS
Network DBMS
Relational DBMS
Object Oriented DBMS
3
What is SQL
  • SQL is the standard used to manage data in
    relational tables. Structured Query Language
    normally referred as SQL and pronounced as SEE QU
    EL..
  • SQL allows users to create databases, add data,
    modify and maintain data. It is governed by
    standards maintained by ISO(International
    Standards Organization).
  • Example of a relational table
  • Employee Department

Emp Id Emp Name Age Dept_id
1 John 40 1
2 Linda 35 1
3 Max 30 2
Dept_id Dept_name
1 Accounts
2 Production
4
SQL syntax and query
  • SQL is case in-sensitive, that is keyword SELECT
    and select is same for SQL. Every SQL command
    should end with a semi-colon ().
  • If the syntax is not proper, then executing the
    command would result in syntax error.
  • Command used to fetch data from table is called
    as query. A basic SQL query consists of SELECT,
    FROM and WHERE clause.
  • SQL SELECT Command example
  • SELECT col1, col2, col3,..
  • FROM table_name
  • WHERE condition

5
Datatypes in SQL
  • Data type is the attribute of the data expected
    in the column. Depending upon the SQL
    implementation (version) different data types are
    available. Whenever you create a column using the
    data type, the SQL Implementation program would
    allocate appropriate amount of space to store the
    data.
  • Data types are broadly categorized into Numerics,
    Strings, Binary, Datetime, Collection and XML.
    Most commonly used data types are Integer,
    Decimal, Float, Character, Varchar, Boolean,
    Date.
  • Example
  • Boolean Value Return value True
  • INT 5400, -2500
  • Numeric or Decimal Decimal(4,2) 1000.24

6
Operators and expressions in SQL
  • In SQL, operators are used in an SQL statements
    WHERE clause to perform different operations like
    comparison, logical and arithmetic operations.
    Operators that can be used are logical,
    comparison and Arithmetic. The Operators include
    lt,gt,ltgt,AND,OR,BETWEEN,ISNULL,,-,?, etc.
  • Generally in any programming language, expression
    is a combination of values, constants, variables,
    operators and functions that results in a value.
    In SQL, expressions are used to query
    database/table to get specific data.
  • Syntax
  • SELECT colsexpressions FROM table_name
  • WHERE condition(using operatorsexpressions)

7
SQL commands can be used to create databases,
edit data in tables, delete data and maintain
data in tables. SQL commands are classified into
3 groups DDL(Data definition Language), DML(Data
Manipulation Language) and DCL(Data Control
Language).
Let us start with CREATE
8
Create Database and Table
  • As part of the Data Definition Language (DDL)
    under SQL, CREATE keyword is used to create
    databases and tables.
  • One would need to first create Database and then
    under that database create the required tables.
  • Syntax for creation of database
  • CREATE DATABASE database_name
  • The SQL CREATE TABLE statement allows you to
    create and define tables in a database.
  • Syntax for creation of table
  • CREATE TABLE tablename
  • ( column1 datatype NULL/NOT NULL,
  • Column2 datatype NULL/NOT NULL
  • PRIMARY KEY (column name or names),
  • FOREIGN KEY(column name) REFERENCES
    Tablename(column name)
  • )
  • WE will learn about Primary and Foreign key in
    the constraints slide

Optional
9
Create Database and Table-1
  • Example
  • Create employee table, which has a constraint on
    Department id of Departments table(i.e. value of
    department id in employee table depends on the
    value of Department id in Departments table).
  • CREATE TABLE employee(
  • Emp_ID INT NOT NULL,
  • Emp_Name Varchar(20) NOT NULL,
  • AGE INT NOT NULL,
  • Phone_Num INT,
  • Dept_ID INT NOT NULL,
  • PRIMARY KEY(Emp_ID),
  • FOREIGN KEY(DEPT_ID) REFERENCES
    DEPARTMENTS(DEPT_ID))

10
Create Database and Table-2
This would create the table with attributes as
below
11
Now that we have created table, let us input data
into it using INSERT INTO statement
12
Insert Into statement
  • Insert Into creates new record(s) in a table.
  • Basic syntax of Insert Into
  • INSERT INTO table_name
  • VALUES (value1, value2, value3,.valueN)
  • This will create 1 record in table.
  • Example
  • INSERT INTO into employee
  • VALUES(1, "john",35,100233023,2)
  • Will give below table

EMP_ID EMP_NAME AGE PHONE_NUM DEPT_ID
1 JOHN 35 100233023 2
13
Insert Into statement-2
  • We can insert records into specific columns as
    well. It is important to note that we can skip
    only those columns which have been defined to
    support NULL values. NOT NULL columns if skipped
    will lead to error message.
  • Syntax
  • INSERT INTO table_name (column1, column2,
    column3,columnN)
  • VALUES (value1, value2, value3,.valueN)
  • Example
  • INSERT INTO employee(emp_id, emp_name, age,
    phone_num, dept_id)
  • values(2,"Linda",30,100234565,1)
  • Will give below table

EMP_ID EMP_NAME AGE PHONE_NUM DEPT_ID
1 JOHN 35 100233023 2
2 LINDA 30 100234565 1
14
Moving on to most used SQL statement SELECT
15
Select Statement
  • SELECT statement is used to retrieve data from
    the tables based on various conditions. The
    tabular result is called result set.
  • Syntax
  • SELECT column names or for all columns from
    table_name
  • 1. Select all columns and data.
  • To retrieve data of all the columns, use like
    shown below
  • SELECT FROM DEPARTMENTS

Dept_Id Dept_Name
1 Accounts
2 Production
3 HR
16
Select Statement-1
  • 2. Retrieve selective columns using column names
  • We can select some of the columns from the table
    by providing the column names in the SELECT
    query.
  • SELECT dept_name FROM DEPARTMENTS

Dept_Name
Accounts
Production
HR
17
Select Statement-3
  • 3. Retrieve selective data from the table based
    on conditions from multiple tables.
  • When we need to retrieve data from multiple
    tables, then those tables should be related to
    each other by at least one field/column.
  • Syntax
  • SELECT col1,table1.col2, table2.col3,colN
  • FROM table1, table2,tableN
  • WHERE Field matching in table1, table2tableN
  • Example Get the Emp_id, Emp_name, Dept_id and
    Dept_Name for the employees.
  • SELECT EMP_ID, EMP_NAME, A.DEPT_ID,B.DEPT_NAME
  • FROM EMPLOYEE A, DEPARTMENTS B
  • WHERE A.DEPT_ID B.DEPT_ID
  • There are other options available known as JOINS
    (INNER, LEFT, RIGHT and FULL) and UNIONS to
    retrieve data from multiple tables.

18
Using the UPDATE statement to modify records in
table.
19
Update statement
  • We can update the contents of table using UPDATE
    statement. This is useful for any correction or
    modification of any data in tables. Consider an
    example, where you have inserted employee details
    and then realized that you have made error while
    entering employees to department 3. You can
    correct this using UPDATE statement.
  • Syntax
  • UPDATE table_name
  • SET col11 val1, col2val2,.colNvalN
  • Where condition
  • We can update 1 or multiple rows of a table based
    on a condition of same table or other table.
  • Example
  • UPDATE CUSTOMER
  • SET customer.vendor_area (SELECT
    vendor_list.vendor_area
  • FROM Vendor_list
  • WHERE vendor_list.vendor_id
    customer.vendor_id)
  • This will result in change to vendor area in
    customer table based on the vendor area in vendor
    list for the vendor id.

20
Let us see how to DELETE records from table.
21
DELETE Statement
  • DELETE statement is used to delete one or more
    rows from a table. We can delete records using
    single or multiple conditions or using condition
    of other tables.
  • Syntax
  • DELETE FROM table_name
  • WHERE conditions
  • Example
  • Delete records using 1 condition
  • DELETE FROM EMPLOYEE
  • WHERE DEPT_ID 3
  • This will delete the records from EMPLOYEE table,
    which have DEPT_ID as 3.

22
Let us see how to modify table attributes using
ALTER statement.
23
ALTER table
  • ALTER statement is used to add, modify and delete
    columns in a table and even rename a table.
    Please note that there are different syntaxes in
    different implementations.
  • Example of Adding columns to a table
  • ALTER TABLE EMPLOYEE
  • ADD LEVEL VARCHAR(5)
  • This would add a new column LEVEL with 5 length
    variable character data type.
  • Example of Renaming table
  • ALTER TABLE DEPARTMENTS
  • RENAME TO DEPTS
  • This would rename the table DEPARTMENTS to DEPTS.

24
Now let us see how to delete database/table using
DROP
25
DROP table
  • SQL DROP is used to delete the existing databases
    and tables from the SQL schema.
  • DROP Database
  • This command will delete the entire database.
  •  Syntax of DROP database is as follows
  • DROP DATABASE database_name
  • DROP Table
  • DROP Table statement is used to delete the
    table from the database, it means all the
    definition, constraints, permissions and data
    stored in tables will be deleted.
  • Syntax of DROP table is as follows
  • DROP TABLE table_name
  • It is important that necessary backups are taken
    of the database and tables before deletion. Also
    you need to have administrator rights for
    deleting database or table.

26
Thank You
  • This covers the basics of SQL, for full tutorial
    please visit www.wideskills.com
About PowerShow.com