CS202 Database Systems Lecture 8 PDF

CS202 Database Systems Lecture 8 Prepared By: Dr. Amr AbdelFatah Structured Query Language (SQL) Structured Query Language Chapter Three DAVID M. KROENKE’S DATABASE CONCEPTS, Database Objects and Queries A database consists of multiple user accounts Each user account owns database...

CS202 Database Systems Lecture 8 Prepared By: Dr. Amr AbdelFatah Structured Query Language (SQL) Structured Query Language Chapter Three DAVID M. KROENKE’S DATABASE CONCEPTS, Database Objects and Queries A database consists of multiple user accounts Each user account owns database objects Tables Views Stored programs Etc. Query: command to perform operation on database object Structured Query Language (SQL) ▪ Industry standard query language for most of relational databases 4 Structured Query Language Structured Query Language Acronym: SQL Pronounced as “S-Q-L” Also pronounced as “Sequel” Originally developed by IBM as the SEQUEL language in the 1970s SQL-92 is an ANSI national standard adopted in 1992 Basic SQL Concepts and Commands SQL (Structured Query Language) is used to manipulate the database. There are two basic types of SQL commands: Data Definition Language (DDL) Data Manipulation Language (DML) DDL commands work with the structure of the objects (tables, indexes, views) in the database. DML commands work with the data in the database (i.e.,manipulate the data). DDL Commands Used to create and modify the structure of database objects CREATE ALTER DROP GRANT REVOKE DDL commands execute as soon as they are issued, and do not need to be explicitly saved DML Commands Used to insert, view, and modify database data INSERT UPDATE DELETE SELECT DML commands need to be explicitly saved or rolled back COMMIT ROLLBACK SAVEPOINT Security - Granting Table Privileges Security is the prevention of unauthorized access to the database. Within an organization, the database administrator determines the types of access various users need for the database. Some users might be able to retrieve and update data in the database. Other users might be able to retrieve any data from the database but not make any changes to it. Still other users might be able to access only a portion of the database. Oracle10g User Accounts User account - identified by a unique username and password User schema - all of the objects that the user creates and stores in the database Object owner has privileges to perform all possible actions on an object Creating New User Accounts Done by DBA Syntax: CREATE username IDENTIFIED BY password; Defining Oracle10g Database Tables To create a table, you must specify: Table name Field names Field data types Field sizes Constraints restrictions on the data values that a field can store Creating a Table CREATE TABLE tablename (fieldname1 data_type, (fieldname2 data_type, …) Oracle Naming standers and Conventions Naming standards are Series of rules Oracle Corporation established for naming all database objects From 1 to 30 characters Only alphanumeric characters, and special characters ($ , _, #) Must begin with a letter and can not contain blank spaces or hyphens Are the following names valid? Why? customer order customer-order #order Oracle Data Types Data type: specifies the kind of data that a field stores Assigning a data type provides a means for error checking Data types enable the DBMS to use storage space more efficiently by internally storing different types of data in different ways Data Types Built-in provided by the system Library built by the software vendor or a third party User-defined built by users 16 Basic Built-In Data Types Character VARCHAR2 CHAR NVARCHAR2 / NCHAR Numeric NUMBER Date/Time Others: LONG, RAW, LONG RAW, BLOB Character Data Types VARCHAR2 Stores variable-length character data up to a maximum of 4,000 characters Values in different records can have a different number of characters fieldname VARCHAR2(maximum_size) (e.g.) emp_name VARCHAR2(20); an instance: ‘Jason Chen’ Character Data Types (cont.) CHAR Fixed-length character data (0) AND (credits ” Less than “=“ Less than or Equal to “