Structured Query Language SQL DATABASE SYSTEMS VERSION 1

Structured Query Language (SQL) DATABASE SYSTEMS VERSION 1. 1 Dept of CSE, KUET, Bangladesh 1

Basics of 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 / Tuples 2. Columns / Attributes / Fields 3. Data 2 Dept of CSE, KUET, Bangladesh

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 Dept of CSE, KUET, Bangladesh 3

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 Dept of CSE, KUET, Bangladesh 4

Basic of Table Books contain relatively disorganized data Organize information using model Resulting in a neatly structured set of rows and columns Dept of CSE, KUET, Bangladesh 5

Relation / Table 1. 2. 3. 4. 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. A database contains many relations. Every relations in a database must have distinct names Every column in a relation must have distinct names Every entries in a column must be in the same domain The ordering of columns in a relation is insignificant 6 Dept of CSE, KUET, Bangladesh

Relation / Table (continued) Duplicate rows are not allowed in a relation 6. The ordering of rows is insignificant 7. Multiple values are not allowed in the cells of a relation 8. 2 rows in a relation may contain the same value for 1 columns but not in all (deviation of 5) 5. 7 Dept of CSE, KUET, Bangladesh

Terminology The number of rows in a table is called Cardinality The number of columns in a table is called Degree 8 Dept of CSE, KUET, Bangladesh

SQL Most influential commercially marketed query language. Uses a combination of relational algebra and relationalcalculus constructs. Although SQL refers a query language, it can do much more than a just a query database. ○ Define structure of the data ○ Modify data in database ○ Specify security construct

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 10 Dept of CSE, KUET, Bangladesh

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 11 Dept of CSE, KUET, Bangladesh

Languages in Database Systems Data Control Language (DCL) Privileges, Access Control, Administrative Rights 12 Dept of CSE, KUET, Bangladesh

Data Definition Language (DDL) Dept of CSE, KUET, Bangladesh 13

Create Table Construct An SQL relation is defined using the create table command: create table r (A 1 D 1, A 2 D 2, . . . , An Dn ); r is the name of the relation each Ai is an attribute name in the schema of relation r Di is the data type of attribute Ai Example: create table branch (branch_name char(15), branch_city char(30), assets integer);

Creating a table Dept of CSE, KUET, Bangladesh 15

Creating a table Dept of CSE, KUET, Bangladesh 16

Drop and Alter Table The drop table command deletes all information about the dropped relation from the database. The alter table command is used to add attributes to an existing relation: alter table r add A D where A is the name of the attribute to be added to relation r and D is the domain of A. All tuples in the relation are assigned null as the value for the new attribute. The alter table command can also be used to drop attributes of a relation: alter table r drop A where A is the name of an attribute of relation r Dropping of attributes not supported by many databases

Dropping a table DROP TABLE table_name; DROP TABLE cars; DROP TABLE specs; DROP TABLE stock; Dept of CSE, KUET, Bangladesh 18

Adding a column to a table Dept of CSE, KUET, Bangladesh 19

Adding columns to a table Dept of CSE, KUET, Bangladesh 20

Modifying a single column on a table Dept of CSE, KUET, Bangladesh 21

Modifying columns on a table Dept of CSE, KUET, Bangladesh 22

Dropping columns on a table Dept of CSE, KUET, Bangladesh 23

Renaming columns on a table Dept of CSE, KUET, Bangladesh 24

Data Modification Language (DML) Dept of CSE, KUET, Bangladesh 25

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’). Dept of CSE, KUET, Bangladesh 26

Inserting data into table Dept of CSE, KUET, Bangladesh 27

Showing data in a table Dept of CSE, KUET, Bangladesh 28

Updating data in a table Dept of CSE, KUET, Bangladesh 29

Deleting row from a table 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! Dept of CSE, KUET, Bangladesh 30

Data Control Language (DCL) Dept of CSE, KUET, Bangladesh 31

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 objects Controls 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 32 Dept of CSE, KUET, Bangladesh

Database Objects An Oracle database consists of multiple user accounts Each user account owns database objects Tables Views Stored programs 33 Dept of CSE, KUET, Bangladesh

Oracle Naming Standard Oracle database objects must adhere to the Oracle Naming Standard 1 to 30 characters long Must begin with a character Can contain characters, numbers, and the symbols $, _ , and # 34 Dept of CSE, KUET, Bangladesh

Creating New User Accounts Done by DBA Syntax: CREATE USER username IDENTIFIED BY password; Dept of CSE, KUET, Bangladesh 35

Granting System Privileges Done by DBA Syntax: GRANT privilege 1, privilege 2, … TO username; Dept of CSE, KUET, Bangladesh 36

So, now can you log in? Yes, you can! 37 Dept of CSE, KUET, Bangladesh

Example Oracle System Privileges Privilege Leve Purpose l CREATE SESSION User Connecting to database CREATE TABLE User Creating tables in current user schema UNLIMITED TABLESPACE User Allows user to create schema objects using as much space as needed CREATE USER DBA Creating new users GRANT ANY PRIVILEGE DBA Granting system privileges to users CREATE ANY TABLE DBA Creating tables in any user schema DROP ANY TABLE DBA Dropping tables in any user schema Dept of CSE, KUET, Bangladesh 38

Granting another privilege 39 Dept of CSE, KUET, Bangladesh

Can you create table yet? 40 Dept of CSE, KUET, Bangladesh

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. Dept of CSE, KUET, Bangladesh 41

… & The Solution 42 Dept of CSE, KUET, Bangladesh

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 43 Dept of CSE, KUET, Bangladesh

Creating a Role Syntax: CREATE ROLE role_name; Dept of CSE, KUET, Bangladesh 44

Granting Privilege to Role GRANT privilege 1, privilege 2, … TO role_name; Dept of CSE, KUET, Bangladesh 45

Assigning Role to the User Syntax: GRANT role_name TO user_name; Dept of CSE, KUET, Bangladesh 46

Revoking System Privileges Syntax: REVOKE privilege 1, privilege 2, … FROM username; Dept of CSE, KUET, Bangladesh 47

What Happens Next? 48 Dept of CSE, KUET, Bangladesh

Oracle Data Types § Data type: specifies type of data stored in a field § Date, character, number, etc. 49 Dept of CSE, KUET, Bangladesh

Character Type Data Type § VARCHAR 2 § Variable-length character strings § Maximum of 4, 000 characters § Must specify maximum width allowed § Example declaration: student_name VARCHAR 2(30) 50 Dept of CSE, KUET, Bangladesh

Character Type Data Type § CHAR § Fixed-length character data § Maximum size 2000 characters § Must specify maximum width allowed § Example declaration: student_gender CHAR(1) 51 Dept of CSE, KUET, Bangladesh

Character Type Data Type § NCHAR § Used for alternate alphabets 52 Dept of CSE, KUET, Bangladesh

Number Data Type § NUMBER § stores values between 10 -130 and 10126 § General declaration format: variable_name NUMBER(precision, scale) 53 Dept of CSE, KUET, Bangladesh

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 54 Dept of CSE, KUET, Bangladesh

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) 55 Dept of CSE, KUET, Bangladesh

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) 56 Dept of CSE, KUET, Bangladesh

Floating Point Numbers Contain a variable number of decimal places § Precision and scale are omitted § § Sample declaration: s_GPA NUMBER 57 Dept of CSE, KUET, Bangladesh

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 58 Dept of CSE, KUET, Bangladesh