S Q L •What is SQL? •Importance of SQL •Should you learn SQL? •Pre-requisites •SQL Topics Covered
What is SQL? This slide and next two slides will give you information on SQL for you to decide if you want to take this course to learn SQL. SQL is an abbreviation for “Structured Query Language”. It is a language used to query data present in relational databases. Though it is called a query language, the language can be used to add data to a database, modify existing data in the database and delete data from the database. SQL is similar to other programming languages such as C, C++ and Java, but with one major difference: it is primarily non-procedural unlike other general programming languages which are procedural. Its queries tell an RDBMS what to retrieve and not how to retrieve.
Importance of SQL SQL is the primary database language used in thousands of applications. SQL is the main, or almost the only language, used in all most all relational database management systems. All market leading database management systems such as Oracle, MS SQL Server and DB2 support SQL. Most popular free database management systems such as MySQL, SQLite and postgreSQL also support SQL. Most of the modern database applications for domains such as banking and telecom are written using SQL. SQL can be used from various other languages such as C, C++, C#, Java, PHP, Python, etc., for writing database applications.
Should You Learn SQL? You should definitely learn SQL if you want to become a database professional. If you are a student and studying a database related course, you may want to take this course to improve your knowledge about SQL and it may help you understand the database course better. Even if you already know SQL, you may want to take this course to refresh your knowledge of SQL. If you are a technical project manager, have some idea of what is SQL and would like to know a bit more about SQL in shortest possible time, you may want to take the course.
Pre-requisites It is preferable if you to meet the following pre-requisites to benefit from this course on SQL: 1. You should already be familiar with database concepts, particularly relational database systems (relational databases and relational database management systems) 2. You should have knowledge of at least one programming language such as C, C++, C#, Java, PHP, python, etc., to appreciate SQL. 3. You should have a computer system such as Windows PC. 4. You should be able to download and install at least MySQL database software for practicing SQL. Alternatively, you can use any other relational database management system such as Oracle, SQL Server, and SQLite
SQL Topics Covered The course on SQL covers the following topics:  Introduction to SQL  Database Structure  MySQL Setup  SQL Basics  SQL - DDL  SQL - DML  SQL - DCL
1. Introduction to SQL  What is SQL?  SQL – A Query Language  SQL Vs Other Languages  History of SQL  Importance of SQL
What is SQL? SQL is an abbreviation for “Structured Query Language”. SQL is a language used to build database applications that need to query relational databases. SQL has statements such as CREATE, SELECT, INSERT, UPDATE, DELETE, etc., just like there are statements such as assignment statement, if statement, while statement, etc., in general purpose programming languages such as C, C++ and Java. SQL is a language for databases just like C, C++ and Java are languages for general purpose programming.
SQL - A Query Language SQL is a Query Language. Query means retrieval of required data from databases. In a general sense it may also mean any statement of SQL that can be used to query database, modify the database or do any other operation on the database. For example, if a database contains data about all employees of a company, the following could be some sample queries expressed in plain English language: • Retrieve salary of employee ‘Rahul’. • Retrieve all data about employees of department ‘Research’. • Retrieve names of employees whose salary is more than Rs 1,00,000. Above queries can be represented in SQL to retrieve data from the database.
Categories of SQL Statements As already mentioned, SQL not only contains query statements, it also contains various other statements required for database management and applications. The broad categories of SQL Statements are as follows: Data Definition Language (DDL) Statements Data Manipulation Language (DML) Statements Data Control Language (DCL) Statements. Note that SQL standard document does not use the terminology DDL, DML and DCL but most of the database text books use it.
SQL - DDL Statements SQL – DDL statements are used to create relational data structure in the database. The DDL statements are used to create database objects such as tables and views, alter them and drop them. Sample DDL statements are: • CREATE TABLE - Used to create a table. • ALTER TABLE – Used to alter definition of a table. • DROP TABLE – Used to remove a table from database.
SQL – DML Statements SQL DML statements are used to query as well as modify the database. Sample DML statements are as follows: • SELECT - Used to retrieve data from one or more tables. • INSERT - Used to add data to a table. • UPDATE – Used to modify data in a table. • DELETE - Used to delete data from a table.
SQL – DCL Statements SQL DCL Statements are used to control access to the database, control transactions and control user sessions with the database. Sample DCL statements are • CONNECT - Used to establish connection to a database. • SET TRANSACTION – Used to set various properties of transactions. • GRANT – Used to provide privileges to access database objects. • COMMIT - Used to make a transaction changes permanent in the database.
History of SQL • 1974 – D. Chamberlin of IBM defined SEQUEL • 1975 – SQUARE language was defined • 1976 – Revised SEQUEL/2 was renamed to SQL
. • 1987 - First ISO Standard for SQL was published. • 1989 - ISO published an addendum on IEF • 1992 - First major revision, aka, SQL2 or SQL-92. • Other revisions were released in 1999 (O-R features), 2003 , 2008 and 2011 History of SQL (contd…1)
Importance of SQL  SQL is the most widely used language for Relational Database Management Systems.  SQL is the primary database language used in thousands of database applications.  Standard exists for SQL; standards ensure database applications are portable across various database management systems.  Supported by products of many organizations.  SQL influences even other standards.
Importance of SQL (contd…1)  All market leading database management systems such as Oracle, MS SQL Server and DB2 support SQL.  Most popular free database management systems such as MySQL, SQLite and postgreSQL also support SQL.  Most of the modern database applications for domains such as banking and telecom are written using SQL.  SQL can be used from various other languages such as C, C++, C#, Java, PHP, Python, etc., for writing database applications.
What You Have Learnt !  SQL is a language for databases.  SQL has statements like statements in programming languages such as C, C++ and Java.  SQL has DDL, DML and DCL statements.  SQL Originated from work started at IBM in 1974 by D. Chamberlin.  SQL is SEQUEL/2 renamed in 1976.  First ISO Standard for SQL was published in 1987.  First major standard is SQL 2 or SQL 1992.  SQL is the most widely used database query language in the world.
2.Relational Database Structure  Organization of Database Objects  Database Objects Directly in the Database  Database Objects in Schemas  Database Objects in Catalogs  Schema  Catalog
Organization of Database Objects A database object is an element such as a table in a database. Some of the Database organizations are as follows: One or more database objects such as tables directly in the database. One or more schemas where each schema contains database objects such as tables. One or more catalogs where each catalog contains one or more schemas. It is possible that some database could have a different organization for its objects.
Database Objects Directly in the Database Table 1 Table 2 View 1 Database directly containing tables, views, etc. Index 1 Database
Database Objects in Schemas Database containing tables, views , etc., in schemas of a database. Database Index 1 View 1Table 2 Table 1 Schema 1 Schema 3 Schema 2
Database Objects in Schemas of Catalogs Database Index 1 View 1Table 2 Table 1 Schema 1 Schema 3 Schema 2 Catalog 1 Catalog 2
Object Containment Hierarchy Database Catalog Schema Table View Index Catalog Catalog Schema Schema
Schema A schema is a named collection of database objects such as tables and views. Each schema has a name. SQL standard provides statements to create and destroy schemas. Most RDBMSs support the concept of schemas. Some RDBMSs simulate schema using user name or owner name.
Catalog A catalog is a named collection of schemas. Each catalog has a name. SQL standard does not specify how to create a catalog. The creation and destruction are specific to an RDBMS. Catalog == Database if an RDBMS does not support catalogs explicitly. An RDBMS installation usually supports one or more databases.
What You Have Learnt ! 1. SQL operates on the objects of relational database structure. 2. An installation of an RDBMS usually supports one or more databases. 3. Method of creation and destruction of databases is not part of SQL standard and hence the method varies for each RDBMS. 4. The physical database structure used to implement the relational database structure could be different for each RDBMS. 5. A database consists of database objects directly in the database or the objects could be in one or more schemas which could be in one or more catalogs. 6. Some RDBMSs use user ID for simulating schemas.
3. MySQL Setup  Downloading and Installing MySQL  Starting MySQL Database Service  Using MySQL Command Line Tool  Creating Database  Creating User Accounts
MySQL Download • We will use SQL command line tool of MySQL RDBMS for demonstrations. • If you do not have MySQL on your machine, you can download MySQL Community Server from (http://www.mysql.com/downloads/mysql/) • Install it on your Windows PC. • Make sure the MySQL server is running before you start its client tool as shown in the next slide.
MySQL Database Service
MySQL Command Line Client You can access the tool from Programs menu as shown below:
MySQL Starting Command Line Client From Windows Command Shell • You can also directly execute the tool from Windows Command Shell. • The tool program file is mysql.exe • It will be present in the following directory: C:Program FilesMySQLMySQL Server 5.1bin • You can invoke it as follows:
MySQL User and Database Creation Use CREATE DATABASE statement of MySQL to create a database, say, mdb, a database for a mobile software development company. Using super user ID root is not a good idea for training purpose to avoid doing any unintended costly changes to the databases. Hence, create a sample user “mdba” with password “mdba123” as shown in the following screenshot and make the user a database administrator for the database by granting all permissions on the database to the user.
MySQL Login to MySQL Run Windows Command Shell and execute MySQL command tool, mysql, with the newly created user and database and supply password as shown in the following MySQL snapshot to use the tool and execute SQL Statements:
4.SQL Basics • Lexical elements • Data Types • Literals • Scalar Expressions • Query Expressions • Predicates
Topics We are going to cover the following topics in this section:  Lexical Elements  Data Types  Literals
Lexical Elements Tokens, Comments Simple Latin Letter, Digit, Special Character Key words, Reserved words, Literals, Identifiers,
Lexical Elements A Lexical Element is a group of characters of SQL that can legally appear in an SQL statement. There are two kinds of lexical elements: • Tokens • Comments A token is a smallest independent unit of the language. A comment is text ignored by SQL implementations that process or execute SQL statements. Comments are used for explaining statements and context of the statements to the human readers.
SQL Character As mentioned in earlier slide, lexical elements of SQL are groups of characters. Each SQL character falls into one of the following categories:  simple Latin letter  digit  special character
Simple Latin Letter A simple Latin letter in SQL is  any upper case Latin letter from A to Z,  any lower case Latin letter from a to z.
Digit A digit is 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
Special Character An SQL special character is a character other than Latin letters and digits. SQL special characters are: “ , ‘, <space>, %, &, *, (, ), +, -, , , ., :, ;, ?, <, =, >, [, ], _, |, {, }, ^.
Tokens Token in SQL is a sequence or group of SQL characters separated by comments, white space or other SQL special character.  A token can be a key word such as SELECT or INSERT.  A token can be a literal such as 123 or ‘John’.  A token can be an identifier such as eno or salary.  A token can be a special character such as ( or =  and so on… Each SQL statement is formed of various kinds of tokens.
Key words A key word in SQL is a reserved word or a non-reserved word. Reserved words are words such as SELECT, INSERT, AND, OR, etc., that have special meaning in the language and these can not be used as identifiers for naming database objects.. Non-reserved words have special meaning only in some specific contexts and hence can be used as identifiers in other contexts. SQL Standard document gives a list of both kinds of key words..
Literals A literal in SQL is a constant value used in SQL statements. It can be used for inserting into columns of a table, for updating existing column values, for comparing with columns and in various expressions that can be used in SQL statements. It is represented using SQL characters depending on the data type it is expected to indicate. For example, an integer literal is 123 whereas a character literal is ‘David’.
Comments There are two types of comments in SQL: • Simple comment • Bracketed comment Simple comment starts with two dashes or minus signs and ends with a newline character. Example: -- This is a simple comment. A bracketed comment starts with /* followed by text of the comment and ends with */. Example: /* This is a bracketed comment. */
Identifiers SQL Identifiers are used for names of database objects such as tables, columns and constraints.  Characters used in identifiers should be in a character set.  Default character set consists of A..Z, a..z, 0..9 and _.  Maximum size of an identifier is 128 characters  An identifier should start with a letter.  An identifier can not contain spaces unless delimited by a pair of double quotes.
Identifiers Example Valid and Invalid Identifiers Valid Identifiers Likely use Employees Name of a table Salary Name of a column pk_emp Name of primary key Warehouse_05 Name of a table “First Name” A column name Invalid Identifiers Reason why it is invalid (Employees) Characters ( and ) are not part of default character set Employee Salary Spaces in the name pk-emp Character – is not part of default character set 5th_warehouse Starts with digit
SQL Data Types
List of SQL Data Types Data type category Data type name Purpose Boolean BOOLEAN To represent value TRUE or FALSE Character CHAR VARCHAR To represent character data for columns such as names of persons or cities. Bit BIT To represent bit 0 or 1 or string of such bits. Note: Removed from SQL 2003 std. Exact numeric NUMERIC DECIMAL BIGINT INTEGER SMALLINT To represent numbers such as 1, 2, 3, 456 and so on. It is also used for numbers with fractions such as 125.68. Approximate numeric FLOAT REAL DOUBLE PRECISION To represent numbers with fractions such as 125.68 where precision of the fraction may not be preserved across manipulation of such column values.
List of SQL Data Types (contd…) Data type category Data type name Purpose Datetime DATE TIME TIMESTAMP To represent date, time and timestamp that contains date and time values. Interval INTERVAL To represent datetime intervals Character large objects CLOB To represent large amounts of character data such as resumes, notes, stories and descriptive text. Introduced in SQL 2003 Binary large Objects BLOB To represent large amounts of binary data such as photographs, pictures, audio files and video files. Introduced in SQL 2003
List of Non-Standard SQL Data Types Data type name RDBMS Purpose BINARY VARBINARY SQLServer MySQL ctreeACE To represent sequence of arbitrary bytes of data. SERIAL MySQL Used for serial numbers that are auto incremented. DATETIME MySQL Same as TIMESTAMP but allows values from a bigger range. YEAR MySQL To represent two or four digit year values. TINYTEXT MEDIUMTEXT LONGTEXT MySQL For CLOB data of various maximum sizes. TINYBLOB MEDIUMBLOB LONGBLOB MySQL For BLOB data of various maximum sizes.
List of Non Standard SQL Data Types (Contd) Data type name RDBMS Purpose ENUM MySQL To represent a value that is from a specific set of character string values. SET MySQL To represent a set of zero or more values. TINYINT MySQL Ctree-SQL A one byte integer data type.
More Data Type Terminology Term Meaning String data types This term refers to bit or character data types (CHAR and VARCHAR) Numeric data types This term refers to Exact and Approximate numeric data types. Large object data types This term refers to CLOB and BLOB data types
NULL NULL represents unknown value for relation attributes/columns. NULL is used as a value for columns for which data is unknown at the time of adding a row to a table. It can also be used as a value for a column at the time of updating a row and to test if a column in a row is NULL. NULL cannot be used if a column is defined not to take NULL values, i.e., the column is defined as a NOT NULL column, i.e., non-nullabe column.
Syntax Conventions Symbol Interpretation Example | Syntax elements separated by this symbol are alternatives. BOOLEAN | INTEGER [ ] An optional syntax element, i.e., the syntax element need not be used. CHAR [(length)] { } Used to group tokens together to indicate contents of the flower brackets is a one syntax element. {CHARACTER | CHAR} [(length)] ... Previous syntax element can appear zero or more times. (column-name [, column-name] ...) The following conventions are used in representing syntax of SQL:
Syntax Conventions (contd) Textual element Interpretation Examples Tokens in capital letters Key words of SQL. Case of the letters of the key words does not matter in actual SQL statements. CREATE INSERT WHERE AND Tokens with one or more words in small letters separated by hyphen These represent identifiers, i.e., names of database objects or properties of these objects such as names of tables, columns and constraints that need to be replaced with actual identifiers while writing SQL statements. table table-name column column-name col-list
Data Type BOOLEAN Values of BOOLEAN column: TRUE or FALSE Syntax: BOOLEAN Example: Definition of a column is_manager in a table: is_manager BOOLEAN
Data Type CHARACTER Data type CHARACTER or CHAR is used for columns in which a sequence of characters such as names of persons, cities, etc., need to be stored. Syntax: {CHARACTER | CHAR } [(length)] length: Length of the string. 1 if not specified. Examples: book_title CHARACTER(50) book_category CHAR Note: CHAR is a fixed-length data type.
Data Type CHAR (contd) Example column: door_type CHAR(3) Inserted Value Actual Value Inserted ‘IN’ ‘IN ’ ‘OUT’ ‘OUT’ ‘INOUT’ Nothing is inserted due to error.
Data Type VARCHAR VARCHAR data type means VARYING CHARACTER data type where size of data stored in such a column in each row can vary according to the actual size of the data. If data size is less than the size specified for the column at the time of its definition, data is not padded with blanks. Syntax: { VARCHAR | CHARACTER VARYING | CHAR VARYING } [ (length) ] Example: book_synopsis VARCHAR(500)
Data Type INTEGER INTEGER data type is used to store integers in the range of -2147483648 to 2147483647. Number of bytes occupied in a column by integer is 4 bytes. Syntax: INTEGER Examples: book_sno INTEGER order_no INTEGER
Data Type SMALLINT SMALLINT data type is used to store integers in the range of -32768 to 32767. Number of bytes occupied in a column by SMALLINT is 2 bytes. Syntax: SMALLINT Examples: roll_no SMALLINT quantity SMALLINT
Data Type NUMERIC NUMERIC data type is used to store numbers that can have fractions. Syntax: NUMERIC [ ( precision [, scale] ) ] precision – Total number of digits including number of decimal places but excluding decimal point. Default value is RDBMS specific. scale – Number of decimal places. Default value is 0. Examples: price NUMERIC (8,2) discount NUMERIC (4,1) interest NUMERIC (4,2)
NUMERIC Data Type Limits RDBMS Maximum Precision Maximum Scale Default Precision Default Scale MySQL 65 30 10 0 MS SQL Server 38 38 18 0 Ctree ACE 32 32 32 0
Data Type DECIMAL DECIMAL data type for all practical purposes is same as NUMERIC data type in most RDBMSs.
Data Type REAL The REAL data type is an approximate numerical data type. Syntax: REAL Size: 4 bytes Examples: salary REAL width REAL
Data Type FLOAT The FLOAT data type is an approximate numerical data type. Syntax: FLOAT [precision] precision – This indicates precision of mantissa. Size: 8 bytes Examples: distance FLOAT
Data Type DOUBLE PRECSION The DOUBLE PRECISION data type is an approximate numerical data type. Syntax: DOUBLE PRECISION Size: 8 bytes Examples: distance DOUBLE PRECSION
Datetime Data Types Datetime data types are used to store points in time in columns. Datetime data types are DATE, TIME and TIMESTAMP. Each datetime data type consists of a meaningful combination of the following fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR and TIMEZONE_MINUTE.
Data Type DATE DATE data type is used to store calendar date in columns. DATE consists of datetime fields: YEAR, MONTH and DAY. Syntax: DATE Examples: date_of_birth DATE join_date DATE
DATE Value Ranges RDBMS Minimum Date Maximum Date MySQL 1000-01-01 9999-12-31 Oracle 4712-01-01 BCE 4712-12-31 CE MS SQL Server 1753-01-01 1900-01-01 9999-12-31 2079-06-06 ctreeACE 1700-03-01 9999-12-31
Data Type TIME TIME data type is used to store time of a day in columns. TIME consists of datetime fields: HOUR, MINUTE and SECOND and fields TIMEZONE_HOUR and TIMEZONE_MINUTE if WITH TIIMEZONE option is used. Syntax: TIME [(precision)] [WITH TIMEZONE] precision: This indicates precision required in seconds value. Default is 0 which means whole seconds A value 3 means milliseconds and 6 means microseconds Examples: start_time TIME event_time TIME(6)
Data Type TIMESTAMP TIMESTAMP data type is used to store date and time of a day in columns. TIMESTAMP consists of datetime fields: YEAR, MONTH, DAY, HOUR, MINUTE and SECOND and fields TIMEZONE_HOUR and TIMEZONE_MINUTE if WITH TIMEZONE option is used. Syntax: TIMESTAMP [(precision)] )] [WITH TIMEZONE] precision: This indicates precision required in seconds value. Default is 6 which means microseconds. A value 3 means millisecond . Examples: transaction_ts TIMESTAMP event_ts TIMESTAMP(6)
Data Type CHARACTER LARGE OBJECT (CLOB)) CHARACTER LARGE OBJECT or CLOB data type is used to store large amount of character data. Syntax: { CHARACTER LARGE OBJECT | CHAR LARGE OBJECT | CLOB } [ ( large-object-length ) ] large-object-length: length [ K | M | G ] [ CHARACTERS | OCTETS ] length: Length of column in characters. Examples: part_description CLOB(5M) emp_resume CLOB(10K)
Data Type BINARY LARGE OBJECT (BLOB) BINARY LARGE OBJECT data type is used to store large amount of binary data. Syntax: { BINARY LARGE OBJECT | BLOB } [ ( large-object-length ) ] large-object-length: length [ K | M | G ] Examples: part_image BLOB(2M) emp_photograph BLOB(150K)
Literals A literal is a valid value for an SQL data type that can be inserted into a column of the data type or existing value of the column can be updated with the value. It can also be used in expressions to compare values in columns. Term constant is synonymous to the term literal. Example literal values for a column of data type INTEGER could be 1, 2, 3, 596, 123456, etc. A few example literal values for a CHARACTER column used to store names of employees are ‘Akhil’, ‘Sparsh’ and ‘David’’.
Boolean Literals Literals of BOOLEAN data type are TRUE and FALSE Syntax: { true | false } These literals are case insensitive.
Character Literals A character literal consists of one or more characters enclosed in single quotation marks. Character literals can be used as data for columns of data type CHAR, VARCHAR and CLOB. Examples: ‘Mahatma Gandhi’ ‘John’ ‘Where there is a will there is a way’ Length of a literal that can be used for a column depends on the length of the column.
INTEGER Literals INTEGER literals are expressed using numbers having one or more digits. An integer literal is any number in the range of -2147483648 to 2147483647. Examples: 123 -123 1234567890
SMALLINT Literals Like an INTEGER literal, a SMALLINT literal is also a number made up of one or more digits. A SMALLINT literal is any number in the range of -32768 to 32767. Examples: 123 -123 12345
NUMERIC and DECIMAL Literals NUMERIC and DECIMAL literals are numbers with decimal part. Examples: 20.75 12345.678 The precision and scale depends on the support provided by the RDBMS product you are using.
Approximate Numeric Literals An approximate numeric literal consists of a mantissa and an exponent. The value of the literal is equivalent to the mantissa multiplied by 10 to the power of the exponent. The approximate numeric literal is used to represent values of data types REAL, FLOAT and DOUBLE PRECISION. Syntax: mantissa E exponent mantissa: An exact numeric literal. exponent: A signed integer. Examples: 5 E 4 This means 50000 10.2 E 3 This means 10200
DATE Literals Date literals are specified with key word DATE followed by a string containing YEAR, MONTH and DAY values. Syntax: DATE date string date string: A string containing date in the format “YYYY-MM-DD” where YYYY is a four digit year, MM is a month (1 to 12) and DD is a day of the month Examples: DATE ‘1947-08-15’ DATE ‘2014-01-03’
TIME Literals Time literals are specified with key word TIME followed by a string containing HOUR, MINUTE, SECOND and optionally fraction of second values. Syntax: TIME time string time string: A string containing date in the format “HH:MM:SS.F” where HH is a two digit hour value (0 to 23), MM is two digit minutes value (0 to 59), DD is a two digit seconds value (0 to 59) and F is a fraction usually either 2, 3, 6 or 9 digit value. Examples: TIME ‘09:00:25’ TIME ‘14:30:15.123456’
TIMESTAMP Literals Timestamp literals are specified with key word TIMESTAMP followed by a date string, a space and time string. Syntax: TIMESTAMP date string space time string The date string and time string are as explained in the previous slides. space is a single blank character. Examples: TIMESTAMP ‘1947-08-15 23:59:59’ TIMESTAMP ‘2014-01-03 14:30:15.123456’ Note that you should specify only one space character (blank) between date and time strings.
BINARY String Literals Binary String Literals are used to store data in columns of data type BLOB, BINARY or VARBINARY columns. A Binary Literal consists of one or more pairs of hexadecimal digits enclosed in a pair of single quotes prefixed by letter X. Syntax: X ‘{hexit hexit}…’ hexit: A hexadecimal digit which is a digit or any of the letters A to F or a to f. Examples: X ‘ABCD’ X ‘a12bc2d4’
What You Have Learnt !  Lexical Elements of SQL  Tokens and Comments  SQL Character  Simple Latin Letters, Digits and Special Characters  Key words – reserved words and non-reserved words  Literals  Simple and Bracketed comments  Identifiers  SQL Data Types  NULL  Literals for each Data Type

Database Systems - Introduction to SQL (Chapter 3/1)

  • 1.
    S Q L •Whatis SQL? •Importance of SQL •Should you learn SQL? •Pre-requisites •SQL Topics Covered
  • 2.
    What is SQL? Thisslide and next two slides will give you information on SQL for you to decide if you want to take this course to learn SQL. SQL is an abbreviation for “Structured Query Language”. It is a language used to query data present in relational databases. Though it is called a query language, the language can be used to add data to a database, modify existing data in the database and delete data from the database. SQL is similar to other programming languages such as C, C++ and Java, but with one major difference: it is primarily non-procedural unlike other general programming languages which are procedural. Its queries tell an RDBMS what to retrieve and not how to retrieve.
  • 3.
    Importance of SQL SQLis the primary database language used in thousands of applications. SQL is the main, or almost the only language, used in all most all relational database management systems. All market leading database management systems such as Oracle, MS SQL Server and DB2 support SQL. Most popular free database management systems such as MySQL, SQLite and postgreSQL also support SQL. Most of the modern database applications for domains such as banking and telecom are written using SQL. SQL can be used from various other languages such as C, C++, C#, Java, PHP, Python, etc., for writing database applications.
  • 4.
    Should You LearnSQL? You should definitely learn SQL if you want to become a database professional. If you are a student and studying a database related course, you may want to take this course to improve your knowledge about SQL and it may help you understand the database course better. Even if you already know SQL, you may want to take this course to refresh your knowledge of SQL. If you are a technical project manager, have some idea of what is SQL and would like to know a bit more about SQL in shortest possible time, you may want to take the course.
  • 5.
    Pre-requisites It is preferableif you to meet the following pre-requisites to benefit from this course on SQL: 1. You should already be familiar with database concepts, particularly relational database systems (relational databases and relational database management systems) 2. You should have knowledge of at least one programming language such as C, C++, C#, Java, PHP, python, etc., to appreciate SQL. 3. You should have a computer system such as Windows PC. 4. You should be able to download and install at least MySQL database software for practicing SQL. Alternatively, you can use any other relational database management system such as Oracle, SQL Server, and SQLite
  • 6.
    SQL Topics Covered Thecourse on SQL covers the following topics:  Introduction to SQL  Database Structure  MySQL Setup  SQL Basics  SQL - DDL  SQL - DML  SQL - DCL
  • 7.
    1. Introduction toSQL  What is SQL?  SQL – A Query Language  SQL Vs Other Languages  History of SQL  Importance of SQL
  • 8.
    What is SQL? SQLis an abbreviation for “Structured Query Language”. SQL is a language used to build database applications that need to query relational databases. SQL has statements such as CREATE, SELECT, INSERT, UPDATE, DELETE, etc., just like there are statements such as assignment statement, if statement, while statement, etc., in general purpose programming languages such as C, C++ and Java. SQL is a language for databases just like C, C++ and Java are languages for general purpose programming.
  • 9.
    SQL - AQuery Language SQL is a Query Language. Query means retrieval of required data from databases. In a general sense it may also mean any statement of SQL that can be used to query database, modify the database or do any other operation on the database. For example, if a database contains data about all employees of a company, the following could be some sample queries expressed in plain English language: • Retrieve salary of employee ‘Rahul’. • Retrieve all data about employees of department ‘Research’. • Retrieve names of employees whose salary is more than Rs 1,00,000. Above queries can be represented in SQL to retrieve data from the database.
  • 10.
    Categories of SQLStatements As already mentioned, SQL not only contains query statements, it also contains various other statements required for database management and applications. The broad categories of SQL Statements are as follows: Data Definition Language (DDL) Statements Data Manipulation Language (DML) Statements Data Control Language (DCL) Statements. Note that SQL standard document does not use the terminology DDL, DML and DCL but most of the database text books use it.
  • 11.
    SQL - DDLStatements SQL – DDL statements are used to create relational data structure in the database. The DDL statements are used to create database objects such as tables and views, alter them and drop them. Sample DDL statements are: • CREATE TABLE - Used to create a table. • ALTER TABLE – Used to alter definition of a table. • DROP TABLE – Used to remove a table from database.
  • 12.
    SQL – DMLStatements SQL DML statements are used to query as well as modify the database. Sample DML statements are as follows: • SELECT - Used to retrieve data from one or more tables. • INSERT - Used to add data to a table. • UPDATE – Used to modify data in a table. • DELETE - Used to delete data from a table.
  • 13.
    SQL – DCLStatements SQL DCL Statements are used to control access to the database, control transactions and control user sessions with the database. Sample DCL statements are • CONNECT - Used to establish connection to a database. • SET TRANSACTION – Used to set various properties of transactions. • GRANT – Used to provide privileges to access database objects. • COMMIT - Used to make a transaction changes permanent in the database.
  • 14.
    History of SQL •1974 – D. Chamberlin of IBM defined SEQUEL • 1975 – SQUARE language was defined • 1976 – Revised SEQUEL/2 was renamed to SQL
  • 15.
    . • 1987 -First ISO Standard for SQL was published. • 1989 - ISO published an addendum on IEF • 1992 - First major revision, aka, SQL2 or SQL-92. • Other revisions were released in 1999 (O-R features), 2003 , 2008 and 2011 History of SQL (contd…1)
  • 16.
    Importance of SQL SQL is the most widely used language for Relational Database Management Systems.  SQL is the primary database language used in thousands of database applications.  Standard exists for SQL; standards ensure database applications are portable across various database management systems.  Supported by products of many organizations.  SQL influences even other standards.
  • 17.
    Importance of SQL(contd…1)  All market leading database management systems such as Oracle, MS SQL Server and DB2 support SQL.  Most popular free database management systems such as MySQL, SQLite and postgreSQL also support SQL.  Most of the modern database applications for domains such as banking and telecom are written using SQL.  SQL can be used from various other languages such as C, C++, C#, Java, PHP, Python, etc., for writing database applications.
  • 18.
    What You HaveLearnt !  SQL is a language for databases.  SQL has statements like statements in programming languages such as C, C++ and Java.  SQL has DDL, DML and DCL statements.  SQL Originated from work started at IBM in 1974 by D. Chamberlin.  SQL is SEQUEL/2 renamed in 1976.  First ISO Standard for SQL was published in 1987.  First major standard is SQL 2 or SQL 1992.  SQL is the most widely used database query language in the world.
  • 19.
    2.Relational Database Structure Organization of Database Objects  Database Objects Directly in the Database  Database Objects in Schemas  Database Objects in Catalogs  Schema  Catalog
  • 20.
    Organization of DatabaseObjects A database object is an element such as a table in a database. Some of the Database organizations are as follows: One or more database objects such as tables directly in the database. One or more schemas where each schema contains database objects such as tables. One or more catalogs where each catalog contains one or more schemas. It is possible that some database could have a different organization for its objects.
  • 21.
    Database Objects Directly inthe Database Table 1 Table 2 View 1 Database directly containing tables, views, etc. Index 1 Database
  • 22.
    Database Objects in Schemas Databasecontaining tables, views , etc., in schemas of a database. Database Index 1 View 1Table 2 Table 1 Schema 1 Schema 3 Schema 2
  • 23.
    Database Objects in Schemasof Catalogs Database Index 1 View 1Table 2 Table 1 Schema 1 Schema 3 Schema 2 Catalog 1 Catalog 2
  • 24.
    Object Containment Hierarchy Database Catalog Schema TableView Index Catalog Catalog Schema Schema
  • 25.
    Schema A schema isa named collection of database objects such as tables and views. Each schema has a name. SQL standard provides statements to create and destroy schemas. Most RDBMSs support the concept of schemas. Some RDBMSs simulate schema using user name or owner name.
  • 26.
    Catalog A catalog isa named collection of schemas. Each catalog has a name. SQL standard does not specify how to create a catalog. The creation and destruction are specific to an RDBMS. Catalog == Database if an RDBMS does not support catalogs explicitly. An RDBMS installation usually supports one or more databases.
  • 27.
    What You HaveLearnt ! 1. SQL operates on the objects of relational database structure. 2. An installation of an RDBMS usually supports one or more databases. 3. Method of creation and destruction of databases is not part of SQL standard and hence the method varies for each RDBMS. 4. The physical database structure used to implement the relational database structure could be different for each RDBMS. 5. A database consists of database objects directly in the database or the objects could be in one or more schemas which could be in one or more catalogs. 6. Some RDBMSs use user ID for simulating schemas.
  • 28.
    3. MySQL Setup Downloading and Installing MySQL  Starting MySQL Database Service  Using MySQL Command Line Tool  Creating Database  Creating User Accounts
  • 29.
    MySQL Download • Wewill use SQL command line tool of MySQL RDBMS for demonstrations. • If you do not have MySQL on your machine, you can download MySQL Community Server from (http://www.mysql.com/downloads/mysql/) • Install it on your Windows PC. • Make sure the MySQL server is running before you start its client tool as shown in the next slide.
  • 30.
  • 31.
    MySQL Command Line Client Youcan access the tool from Programs menu as shown below:
  • 32.
    MySQL Starting Command LineClient From Windows Command Shell • You can also directly execute the tool from Windows Command Shell. • The tool program file is mysql.exe • It will be present in the following directory: C:Program FilesMySQLMySQL Server 5.1bin • You can invoke it as follows:
  • 33.
    MySQL User and DatabaseCreation Use CREATE DATABASE statement of MySQL to create a database, say, mdb, a database for a mobile software development company. Using super user ID root is not a good idea for training purpose to avoid doing any unintended costly changes to the databases. Hence, create a sample user “mdba” with password “mdba123” as shown in the following screenshot and make the user a database administrator for the database by granting all permissions on the database to the user.
  • 34.
    MySQL Login to MySQL RunWindows Command Shell and execute MySQL command tool, mysql, with the newly created user and database and supply password as shown in the following MySQL snapshot to use the tool and execute SQL Statements:
  • 35.
    4.SQL Basics • Lexicalelements • Data Types • Literals • Scalar Expressions • Query Expressions • Predicates
  • 36.
    Topics We are goingto cover the following topics in this section:  Lexical Elements  Data Types  Literals
  • 37.
    Lexical Elements Tokens, Comments SimpleLatin Letter, Digit, Special Character Key words, Reserved words, Literals, Identifiers,
  • 38.
    Lexical Elements A LexicalElement is a group of characters of SQL that can legally appear in an SQL statement. There are two kinds of lexical elements: • Tokens • Comments A token is a smallest independent unit of the language. A comment is text ignored by SQL implementations that process or execute SQL statements. Comments are used for explaining statements and context of the statements to the human readers.
  • 39.
    SQL Character As mentionedin earlier slide, lexical elements of SQL are groups of characters. Each SQL character falls into one of the following categories:  simple Latin letter  digit  special character
  • 40.
    Simple Latin Letter Asimple Latin letter in SQL is  any upper case Latin letter from A to Z,  any lower case Latin letter from a to z.
  • 41.
    Digit A digit is0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
  • 42.
    Special Character An SQLspecial character is a character other than Latin letters and digits. SQL special characters are: “ , ‘, <space>, %, &, *, (, ), +, -, , , ., :, ;, ?, <, =, >, [, ], _, |, {, }, ^.
  • 43.
    Tokens Token in SQLis a sequence or group of SQL characters separated by comments, white space or other SQL special character.  A token can be a key word such as SELECT or INSERT.  A token can be a literal such as 123 or ‘John’.  A token can be an identifier such as eno or salary.  A token can be a special character such as ( or =  and so on… Each SQL statement is formed of various kinds of tokens.
  • 44.
    Key words A keyword in SQL is a reserved word or a non-reserved word. Reserved words are words such as SELECT, INSERT, AND, OR, etc., that have special meaning in the language and these can not be used as identifiers for naming database objects.. Non-reserved words have special meaning only in some specific contexts and hence can be used as identifiers in other contexts. SQL Standard document gives a list of both kinds of key words..
  • 45.
    Literals A literal inSQL is a constant value used in SQL statements. It can be used for inserting into columns of a table, for updating existing column values, for comparing with columns and in various expressions that can be used in SQL statements. It is represented using SQL characters depending on the data type it is expected to indicate. For example, an integer literal is 123 whereas a character literal is ‘David’.
  • 46.
    Comments There are twotypes of comments in SQL: • Simple comment • Bracketed comment Simple comment starts with two dashes or minus signs and ends with a newline character. Example: -- This is a simple comment. A bracketed comment starts with /* followed by text of the comment and ends with */. Example: /* This is a bracketed comment. */
  • 47.
    Identifiers SQL Identifiers areused for names of database objects such as tables, columns and constraints.  Characters used in identifiers should be in a character set.  Default character set consists of A..Z, a..z, 0..9 and _.  Maximum size of an identifier is 128 characters  An identifier should start with a letter.  An identifier can not contain spaces unless delimited by a pair of double quotes.
  • 48.
    Identifiers Example Valid andInvalid Identifiers Valid Identifiers Likely use Employees Name of a table Salary Name of a column pk_emp Name of primary key Warehouse_05 Name of a table “First Name” A column name Invalid Identifiers Reason why it is invalid (Employees) Characters ( and ) are not part of default character set Employee Salary Spaces in the name pk-emp Character – is not part of default character set 5th_warehouse Starts with digit
  • 49.
  • 50.
    List of SQLData Types Data type category Data type name Purpose Boolean BOOLEAN To represent value TRUE or FALSE Character CHAR VARCHAR To represent character data for columns such as names of persons or cities. Bit BIT To represent bit 0 or 1 or string of such bits. Note: Removed from SQL 2003 std. Exact numeric NUMERIC DECIMAL BIGINT INTEGER SMALLINT To represent numbers such as 1, 2, 3, 456 and so on. It is also used for numbers with fractions such as 125.68. Approximate numeric FLOAT REAL DOUBLE PRECISION To represent numbers with fractions such as 125.68 where precision of the fraction may not be preserved across manipulation of such column values.
  • 51.
    List of SQLData Types (contd…) Data type category Data type name Purpose Datetime DATE TIME TIMESTAMP To represent date, time and timestamp that contains date and time values. Interval INTERVAL To represent datetime intervals Character large objects CLOB To represent large amounts of character data such as resumes, notes, stories and descriptive text. Introduced in SQL 2003 Binary large Objects BLOB To represent large amounts of binary data such as photographs, pictures, audio files and video files. Introduced in SQL 2003
  • 52.
    List of Non-Standard SQLData Types Data type name RDBMS Purpose BINARY VARBINARY SQLServer MySQL ctreeACE To represent sequence of arbitrary bytes of data. SERIAL MySQL Used for serial numbers that are auto incremented. DATETIME MySQL Same as TIMESTAMP but allows values from a bigger range. YEAR MySQL To represent two or four digit year values. TINYTEXT MEDIUMTEXT LONGTEXT MySQL For CLOB data of various maximum sizes. TINYBLOB MEDIUMBLOB LONGBLOB MySQL For BLOB data of various maximum sizes.
  • 53.
    List of NonStandard SQL Data Types (Contd) Data type name RDBMS Purpose ENUM MySQL To represent a value that is from a specific set of character string values. SET MySQL To represent a set of zero or more values. TINYINT MySQL Ctree-SQL A one byte integer data type.
  • 54.
    More Data TypeTerminology Term Meaning String data types This term refers to bit or character data types (CHAR and VARCHAR) Numeric data types This term refers to Exact and Approximate numeric data types. Large object data types This term refers to CLOB and BLOB data types
  • 55.
    NULL NULL represents unknownvalue for relation attributes/columns. NULL is used as a value for columns for which data is unknown at the time of adding a row to a table. It can also be used as a value for a column at the time of updating a row and to test if a column in a row is NULL. NULL cannot be used if a column is defined not to take NULL values, i.e., the column is defined as a NOT NULL column, i.e., non-nullabe column.
  • 56.
    Syntax Conventions Symbol InterpretationExample | Syntax elements separated by this symbol are alternatives. BOOLEAN | INTEGER [ ] An optional syntax element, i.e., the syntax element need not be used. CHAR [(length)] { } Used to group tokens together to indicate contents of the flower brackets is a one syntax element. {CHARACTER | CHAR} [(length)] ... Previous syntax element can appear zero or more times. (column-name [, column-name] ...) The following conventions are used in representing syntax of SQL:
  • 57.
    Syntax Conventions (contd) Textualelement Interpretation Examples Tokens in capital letters Key words of SQL. Case of the letters of the key words does not matter in actual SQL statements. CREATE INSERT WHERE AND Tokens with one or more words in small letters separated by hyphen These represent identifiers, i.e., names of database objects or properties of these objects such as names of tables, columns and constraints that need to be replaced with actual identifiers while writing SQL statements. table table-name column column-name col-list
  • 58.
    Data Type BOOLEAN Valuesof BOOLEAN column: TRUE or FALSE Syntax: BOOLEAN Example: Definition of a column is_manager in a table: is_manager BOOLEAN
  • 59.
    Data Type CHARACTER Datatype CHARACTER or CHAR is used for columns in which a sequence of characters such as names of persons, cities, etc., need to be stored. Syntax: {CHARACTER | CHAR } [(length)] length: Length of the string. 1 if not specified. Examples: book_title CHARACTER(50) book_category CHAR Note: CHAR is a fixed-length data type.
  • 60.
    Data Type CHAR(contd) Example column: door_type CHAR(3) Inserted Value Actual Value Inserted ‘IN’ ‘IN ’ ‘OUT’ ‘OUT’ ‘INOUT’ Nothing is inserted due to error.
  • 61.
    Data Type VARCHAR VARCHARdata type means VARYING CHARACTER data type where size of data stored in such a column in each row can vary according to the actual size of the data. If data size is less than the size specified for the column at the time of its definition, data is not padded with blanks. Syntax: { VARCHAR | CHARACTER VARYING | CHAR VARYING } [ (length) ] Example: book_synopsis VARCHAR(500)
  • 62.
    Data Type INTEGER INTEGERdata type is used to store integers in the range of -2147483648 to 2147483647. Number of bytes occupied in a column by integer is 4 bytes. Syntax: INTEGER Examples: book_sno INTEGER order_no INTEGER
  • 63.
    Data Type SMALLINT SMALLINTdata type is used to store integers in the range of -32768 to 32767. Number of bytes occupied in a column by SMALLINT is 2 bytes. Syntax: SMALLINT Examples: roll_no SMALLINT quantity SMALLINT
  • 64.
    Data Type NUMERIC NUMERICdata type is used to store numbers that can have fractions. Syntax: NUMERIC [ ( precision [, scale] ) ] precision – Total number of digits including number of decimal places but excluding decimal point. Default value is RDBMS specific. scale – Number of decimal places. Default value is 0. Examples: price NUMERIC (8,2) discount NUMERIC (4,1) interest NUMERIC (4,2)
  • 65.
    NUMERIC Data TypeLimits RDBMS Maximum Precision Maximum Scale Default Precision Default Scale MySQL 65 30 10 0 MS SQL Server 38 38 18 0 Ctree ACE 32 32 32 0
  • 66.
    Data Type DECIMAL DECIMALdata type for all practical purposes is same as NUMERIC data type in most RDBMSs.
  • 67.
    Data Type REAL TheREAL data type is an approximate numerical data type. Syntax: REAL Size: 4 bytes Examples: salary REAL width REAL
  • 68.
    Data Type FLOAT TheFLOAT data type is an approximate numerical data type. Syntax: FLOAT [precision] precision – This indicates precision of mantissa. Size: 8 bytes Examples: distance FLOAT
  • 69.
    Data Type DOUBLEPRECSION The DOUBLE PRECISION data type is an approximate numerical data type. Syntax: DOUBLE PRECISION Size: 8 bytes Examples: distance DOUBLE PRECSION
  • 70.
    Datetime Data Types Datetimedata types are used to store points in time in columns. Datetime data types are DATE, TIME and TIMESTAMP. Each datetime data type consists of a meaningful combination of the following fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR and TIMEZONE_MINUTE.
  • 71.
    Data Type DATE DATEdata type is used to store calendar date in columns. DATE consists of datetime fields: YEAR, MONTH and DAY. Syntax: DATE Examples: date_of_birth DATE join_date DATE
  • 72.
    DATE Value Ranges RDBMSMinimum Date Maximum Date MySQL 1000-01-01 9999-12-31 Oracle 4712-01-01 BCE 4712-12-31 CE MS SQL Server 1753-01-01 1900-01-01 9999-12-31 2079-06-06 ctreeACE 1700-03-01 9999-12-31
  • 73.
    Data Type TIME TIMEdata type is used to store time of a day in columns. TIME consists of datetime fields: HOUR, MINUTE and SECOND and fields TIMEZONE_HOUR and TIMEZONE_MINUTE if WITH TIIMEZONE option is used. Syntax: TIME [(precision)] [WITH TIMEZONE] precision: This indicates precision required in seconds value. Default is 0 which means whole seconds A value 3 means milliseconds and 6 means microseconds Examples: start_time TIME event_time TIME(6)
  • 74.
    Data Type TIMESTAMP TIMESTAMPdata type is used to store date and time of a day in columns. TIMESTAMP consists of datetime fields: YEAR, MONTH, DAY, HOUR, MINUTE and SECOND and fields TIMEZONE_HOUR and TIMEZONE_MINUTE if WITH TIMEZONE option is used. Syntax: TIMESTAMP [(precision)] )] [WITH TIMEZONE] precision: This indicates precision required in seconds value. Default is 6 which means microseconds. A value 3 means millisecond . Examples: transaction_ts TIMESTAMP event_ts TIMESTAMP(6)
  • 75.
    Data Type CHARACTERLARGE OBJECT (CLOB)) CHARACTER LARGE OBJECT or CLOB data type is used to store large amount of character data. Syntax: { CHARACTER LARGE OBJECT | CHAR LARGE OBJECT | CLOB } [ ( large-object-length ) ] large-object-length: length [ K | M | G ] [ CHARACTERS | OCTETS ] length: Length of column in characters. Examples: part_description CLOB(5M) emp_resume CLOB(10K)
  • 76.
    Data Type BINARYLARGE OBJECT (BLOB) BINARY LARGE OBJECT data type is used to store large amount of binary data. Syntax: { BINARY LARGE OBJECT | BLOB } [ ( large-object-length ) ] large-object-length: length [ K | M | G ] Examples: part_image BLOB(2M) emp_photograph BLOB(150K)
  • 77.
    Literals A literal isa valid value for an SQL data type that can be inserted into a column of the data type or existing value of the column can be updated with the value. It can also be used in expressions to compare values in columns. Term constant is synonymous to the term literal. Example literal values for a column of data type INTEGER could be 1, 2, 3, 596, 123456, etc. A few example literal values for a CHARACTER column used to store names of employees are ‘Akhil’, ‘Sparsh’ and ‘David’’.
  • 78.
    Boolean Literals Literals ofBOOLEAN data type are TRUE and FALSE Syntax: { true | false } These literals are case insensitive.
  • 79.
    Character Literals A characterliteral consists of one or more characters enclosed in single quotation marks. Character literals can be used as data for columns of data type CHAR, VARCHAR and CLOB. Examples: ‘Mahatma Gandhi’ ‘John’ ‘Where there is a will there is a way’ Length of a literal that can be used for a column depends on the length of the column.
  • 80.
    INTEGER Literals INTEGER literalsare expressed using numbers having one or more digits. An integer literal is any number in the range of -2147483648 to 2147483647. Examples: 123 -123 1234567890
  • 81.
    SMALLINT Literals Like anINTEGER literal, a SMALLINT literal is also a number made up of one or more digits. A SMALLINT literal is any number in the range of -32768 to 32767. Examples: 123 -123 12345
  • 82.
    NUMERIC and DECIMAL Literals NUMERICand DECIMAL literals are numbers with decimal part. Examples: 20.75 12345.678 The precision and scale depends on the support provided by the RDBMS product you are using.
  • 83.
    Approximate Numeric Literals An approximatenumeric literal consists of a mantissa and an exponent. The value of the literal is equivalent to the mantissa multiplied by 10 to the power of the exponent. The approximate numeric literal is used to represent values of data types REAL, FLOAT and DOUBLE PRECISION. Syntax: mantissa E exponent mantissa: An exact numeric literal. exponent: A signed integer. Examples: 5 E 4 This means 50000 10.2 E 3 This means 10200
  • 84.
    DATE Literals Date literalsare specified with key word DATE followed by a string containing YEAR, MONTH and DAY values. Syntax: DATE date string date string: A string containing date in the format “YYYY-MM-DD” where YYYY is a four digit year, MM is a month (1 to 12) and DD is a day of the month Examples: DATE ‘1947-08-15’ DATE ‘2014-01-03’
  • 85.
    TIME Literals Time literalsare specified with key word TIME followed by a string containing HOUR, MINUTE, SECOND and optionally fraction of second values. Syntax: TIME time string time string: A string containing date in the format “HH:MM:SS.F” where HH is a two digit hour value (0 to 23), MM is two digit minutes value (0 to 59), DD is a two digit seconds value (0 to 59) and F is a fraction usually either 2, 3, 6 or 9 digit value. Examples: TIME ‘09:00:25’ TIME ‘14:30:15.123456’
  • 86.
    TIMESTAMP Literals Timestamp literalsare specified with key word TIMESTAMP followed by a date string, a space and time string. Syntax: TIMESTAMP date string space time string The date string and time string are as explained in the previous slides. space is a single blank character. Examples: TIMESTAMP ‘1947-08-15 23:59:59’ TIMESTAMP ‘2014-01-03 14:30:15.123456’ Note that you should specify only one space character (blank) between date and time strings.
  • 87.
    BINARY String Literals BinaryString Literals are used to store data in columns of data type BLOB, BINARY or VARBINARY columns. A Binary Literal consists of one or more pairs of hexadecimal digits enclosed in a pair of single quotes prefixed by letter X. Syntax: X ‘{hexit hexit}…’ hexit: A hexadecimal digit which is a digit or any of the letters A to F or a to f. Examples: X ‘ABCD’ X ‘a12bc2d4’
  • 88.
    What You HaveLearnt !  Lexical Elements of SQL  Tokens and Comments  SQL Character  Simple Latin Letters, Digits and Special Characters  Key words – reserved words and non-reserved words  Literals  Simple and Bracketed comments  Identifiers  SQL Data Types  NULL  Literals for each Data Type