sql
Post on 12-Sep-2014
288 views
DESCRIPTION
TRANSCRIPT
![Page 1: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/1.jpg)
Structured Query Language (SQL)
1Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh
![Page 2: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/2.jpg)
2
Rushdi Shams, Dept of CSE,
KUET
Basics of Table Table is a bucket where you pour data. Data in a specific table is associated with
all other items in that table. In a table, there are basically 3 things-1. Rows / Records / Tuples2. Columns / Attributes / Fields3. Data
![Page 3: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/3.jpg)
3Rushdi Shams, Dept of CSE, KUET
Basics of Table
In the table, the column goes on in a horizontal fashion.
ISBN, AUTHOR, PUBLISHER, TITLE, GENRE, PRINTED are the column names for this table
![Page 4: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/4.jpg)
4Rushdi Shams, Dept of CSE, KUET
Basics of Table
In the table, the row goes on in a vertical fashion. Every row in this table has data for ISBN, AUTHOR,
PUBLISHER, TITLE, GENRE, PRINTED
![Page 5: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/5.jpg)
5Rushdi Shams, Dept of CSE, KUET
Basic of Table
Books contain relatively disorganized data Organize information using model Resulting in a neatly structured set of rows and
columns
![Page 6: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/6.jpg)
6
Rushdi Shams, Dept of CSE,
KUET
Relation / Table In relational data model, the table is also called
relation. There are set of rules that are applied on the relations. You must have to know them.
1. A database contains many relations. Every relations in a database must have distinct names
2. Every column in a relation must have distinct names3. Every entries in a column must be in the same domain4. The ordering of columns in a relation is insignificant
![Page 7: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/7.jpg)
7
Rushdi Shams, Dept of CSE,
KUET
Relation / Table (continued)
5. Duplicate rows are not allowed in a relation
6. The ordering of rows is insignificant7. Multiple values are not allowed in the cells
of a relation8. 2 rows in a relation may contain the same
value for 1 columns but not in all (deviation of 5)
![Page 8: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/8.jpg)
8
Rushdi Shams, Dept of CSE,
KUET
Terminology The number of rows in a table is called
Cardinality The number of columns in a table is called
Degree
![Page 9: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/9.jpg)
9
Rushdi Shams, Dept of CSE,
KUET
Languages in Database Systems Data Definition Language (DDL) Used to create and modify the structure of
database objects CREATE ALTER DROP
DDL commands execute as soon as they are issued, and do not need to be explicitly saved
![Page 10: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/10.jpg)
10
Rushdi Shams, Dept of CSE,
KUET
Languages in Database Systems Data Modification Language (DML) Used to insert, view, and modify database data
INSERT UPDATE DELETE SELECT
DML commands need to be explicitly saved or rolled back
COMMIT ROLLBACK
![Page 11: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/11.jpg)
11
Rushdi Shams, Dept of CSE,
KUET
Languages in Database Systems Data Control Language (DCL)
Privileges, Access Control, Administrative Rights
![Page 12: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/12.jpg)
Data Definition Language (DDL)Data Definition Language (DDL)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 12
![Page 13: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/13.jpg)
Creating a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 13
![Page 14: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/14.jpg)
Creating a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 14
![Page 15: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/15.jpg)
Dropping a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 15
DROP TABLE table_name; DROP TABLE cars; DROP TABLE specs; DROP TABLE stock;
![Page 16: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/16.jpg)
Adding a column to a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 16
![Page 17: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/17.jpg)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 17
Adding columns to a table
![Page 18: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/18.jpg)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 18
Modifying a single column on a table
![Page 19: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/19.jpg)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 19
Modifying columns on a table
![Page 20: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/20.jpg)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 20
Dropping columns on a table
![Page 21: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/21.jpg)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 21
Renaming columns on a table
![Page 22: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/22.jpg)
Data Modification Language (DML)Data Modification Language (DML)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 22
![Page 23: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/23.jpg)
Inserting data into table
REMEMBER- when you are inserting values that are varchar, char or other string types (if any) then you will have to put ‘’ around that value (e.g. ‘value_for_column_X’).
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 23
![Page 24: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/24.jpg)
Inserting data into table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 24
![Page 25: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/25.jpg)
Showing data in a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 25
![Page 26: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/26.jpg)
Updating data in a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 26
![Page 27: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/27.jpg)
Deleting row from a table
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 27
If you do not put WHERE clause, while using it with DELETE statement then all the data in that table will be deleted. SO, BE CAREFUL!
![Page 28: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/28.jpg)
Data Control Language (DCL)Data Control Language (DCL)
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 28
![Page 29: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/29.jpg)
29
Rushdi Shams, Dept of CSE,
KUET
Types of Database Privileges System Privileges
Control the operations that the user can perform within the database○ Connecting to the database, creating new tables, shutting
down the database, etc. Object Privileges
Granted on individual database objectsControls operations that a user can perform on a
specific object (insert data, delete data, etc.)When you create an object in your user schema, you
can then grant object privileges on that object to other database users
![Page 30: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/30.jpg)
30
Rushdi Shams, Dept of CSE,
KUET
Database Objects An Oracle database consists of multiple user
accounts Each user account owns database objects
TablesViewsStored programs
![Page 31: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/31.jpg)
31
Rushdi Shams, Dept of CSE,
KUET
Oracle Naming Standard Oracle database objects must adhere to the
Oracle Naming Standard1 to 30 characters longMust begin with a characterCan contain characters, numbers, and the
symbols $, _ , and #
![Page 32: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/32.jpg)
32Rushdi Shams, Dept of CSE, KUET
Creating New User Accounts Done by DBA Syntax:
CREATE USER username IDENTIFIED BY password;
![Page 33: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/33.jpg)
33Rushdi Shams, Dept of CSE, KUET
Trying to access the database As we have created a new user and assigned his
password, should we be able to log into the database with the username and password?
![Page 34: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/34.jpg)
34
Rushdi Shams, Dept of CSE,
KUET
The Answer
![Page 35: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/35.jpg)
35Rushdi Shams, Dept of CSE, KUET
Granting System Privileges Done by DBA Syntax:
GRANT privilege1, privilege2, … TO username;
![Page 36: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/36.jpg)
36
Rushdi Shams, Dept of CSE,
KUET
So, now can you log in? Yes, you can!
![Page 37: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/37.jpg)
37Rushdi Shams, Dept of CSE, KUET
Example Oracle System Privileges
PrivilegePrivilege LevLevelel
PurposePurpose
CREATE SESSIONCREATE SESSION UseUserr
Connecting to databaseConnecting to database
CREATE TABLE CREATE TABLE UseUserr
Creating tables in current user Creating tables in current user schemaschema
UNLIMITED UNLIMITED TABLESPACETABLESPACE
UseUserr
Allows user to create schema Allows user to create schema objects using as much space as objects using as much space as neededneeded
CREATE USERCREATE USER DBDBAA
Creating new usersCreating new users
GRANT ANY GRANT ANY PRIVILEGEPRIVILEGE
DBDBAA
Granting system privileges to Granting system privileges to usersusers
CREATE ANY TABLECREATE ANY TABLE DBDBAA
Creating tables in any user Creating tables in any user schemaschema
DROP ANY TABLEDROP ANY TABLE DBDBAA
Dropping tables in any user Dropping tables in any user schemaschema
![Page 38: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/38.jpg)
38
Rushdi Shams, Dept of CSE,
KUET
Granting another privilege
![Page 39: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/39.jpg)
39
Rushdi Shams, Dept of CSE,
KUET
Can you create table yet?
![Page 40: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/40.jpg)
40Rushdi Shams, Dept of CSE, KUET
The Reason You have granted your user to create session (log in)
and to create table. But, it is to create on your default SYSTEM database. Therefore, you will have to grant another privilege to your user.
![Page 41: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/41.jpg)
41
Rushdi Shams, Dept of CSE,
KUET
… & The Solution
![Page 42: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/42.jpg)
42
Rushdi Shams, Dept of CSE,
KUET
Database Roles Role is a database object that can be
assigned system privileges Role is then assigned to a user, and the user
inherits the role’s privileges Used to easily assign groups of related
privileges to users
![Page 43: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/43.jpg)
43Rushdi Shams, Dept of CSE, KUET
Creating a Role Syntax:CREATE ROLE role_name;
![Page 44: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/44.jpg)
44Rushdi Shams, Dept of CSE, KUET
Granting Privilege to RoleGRANT privilege1, privilege2, … TO role_name;
![Page 45: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/45.jpg)
45Rushdi Shams, Dept of CSE, KUET
Assigning Role to the User Syntax:GRANT role_name TO user_name;
![Page 46: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/46.jpg)
46Rushdi Shams, Dept of CSE, KUET
Revoking System Privileges Syntax:REVOKE privilege1, privilege2, …FROM username;
![Page 47: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/47.jpg)
47
Rushdi Shams, Dept of CSE,
KUET
What Happens Next?
![Page 48: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/48.jpg)
48
Rushdi Shams, Dept of CSE,
KUET
Oracle Data Types Data type: specifies type of data stored in a
field Date, character, number, etc.
![Page 49: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/49.jpg)
49
Rushdi Shams, Dept of CSE,
KUET
Character Type Data Type VARCHAR2
Variable-length character strings Maximum of 4,000 characters Must specify maximum width allowed Example declaration:student_name VARCHAR2(30)
![Page 50: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/50.jpg)
50
Rushdi Shams, Dept of CSE,
KUET
Character Type Data Type CHAR
Fixed-length character data Maximum size 2000 characters Must specify maximum width allowed Example declaration:student_gender CHAR(1)
![Page 51: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/51.jpg)
51
Rushdi Shams, Dept of CSE,
KUET
Character Type Data Type NCHAR
Used for alternate alphabets
![Page 52: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/52.jpg)
52
Rushdi Shams, Dept of CSE,
KUET
Number Data Type NUMBER
stores values between 10-130 and 10126
General declaration format:variable_name NUMBER(precision, scale)
![Page 53: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/53.jpg)
53
Rushdi Shams, Dept of CSE,
KUET
Number Data Type Number type (integer, fixed point, floating
point) specified by precision and scale Precision: total number of digits on either side
of the decimal point Scale: number of digits to right of decimal point
![Page 54: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/54.jpg)
54
Rushdi Shams, Dept of CSE,
KUET
Number Data Type Whole number with no digits to right of
decimal point Precision is maximum width Scale is omitted
Sample declaration:s_age NUMBER (2)
![Page 55: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/55.jpg)
55
Rushdi Shams, Dept of CSE,
KUET
Fixed Point Numbers Contain a specific number of decimal places Precision is maximum width Scale is number of decimal places
Sample declaration:item_price NUMBER(5, 2)
![Page 56: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/56.jpg)
56
Rushdi Shams, Dept of CSE,
KUET
Floating Point Numbers Contain a variable number of decimal places Precision and scale are omitted
Sample declaration:s_GPA NUMBER
![Page 57: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/57.jpg)
57
Rushdi Shams, Dept of CSE,
KUET
Date Data Type DATE
Stores dates from 1/1/4712 BC to 12/31/4712 AD Stores both a date and time component
Default date format: DD-MON-YY example: 05-JUN-03
Sample declaration:s_dob DATE
![Page 58: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/58.jpg)
SELECT command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 58
![Page 59: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/59.jpg)
SELECT command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 59
![Page 60: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/60.jpg)
SELECT Command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 60
![Page 61: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/61.jpg)
SELECT Command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 61
![Page 62: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/62.jpg)
SELECT Command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 62
![Page 63: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/63.jpg)
SELECT Command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 63
![Page 64: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/64.jpg)
SELECT Command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 64
![Page 65: Sql](https://reader033.vdocuments.us/reader033/viewer/2022061104/5412cf808d7f72174e8b45dc/html5/thumbnails/65.jpg)
SELECT Command
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 65