SQL Fundamentals - Lecture 2

SQL Fundamentals - Lecture 2

• 1.
  SQL Fundamentals Oracle 11g MU H A M M A D WA H E E D O R AC L E D ATA BA S E D E V E LO P E R E M A I L : m .wa h e e d 3 6 6 8 @ g m a i l . co m . 1 Lecture#1
• 2.
  What is SQL? •StructuredQuery language (SQL) pronounced as "S-Q-L" or sometimes as "See- Quel”( interrogate) •It is language for operating Databases. •It can be effectively used to insert, search, update, delete records optimizing and maintenance of databases. •Mostly databases use SQL like MySQL Database, Oracle, MS SQL server, Sybase etc. • How to use SQL syntaxes? 2
• 3.
  Writing SQL Statements •SQLstatements are not case-sensitive. But we will use UPPERCASE letters for syntax and lowercase for user-defined data. •SQL statements can be on one or more lines. •Keywords cannot be abbreviated or split across lines. •Clauses are usually placed on separate lines. •Indents are used to enhance readability. •In iSQL*Plus, SQL statements can optionally be terminated by a semicolon(;). •In SQL*Plus, you are required to end each SQL statement with a semicolon(;). 3
• 4.
  Naming conventions ofSQL (General) •Name will be unique and does not exist as a reserved/keyword. •Can have maximum of 30 characters. •Use letters, numbers & underscores ( _ ) in names. •Names must begin with a letter and not end with ( _ ). •Avoid consecutive underscores. e.g. first__name •Use underscores instead of space. •Names should be meaningful. •If you have to use abbreviation make sure they are commonly understood. 4
• 5.
  SQL statements 5
• 6.
  Connect DBMS •There aredifferent user categories due to security/access control. •Database Administrators •Application Developers •Application Administrators •Database Users •Here is the link to Oracle’s site for detailed knowledge on user types and their roles: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ dba001.htm#ADMIN11013 6
• 7.
  Database Users(Administrator) •In additionto the user accounts that you create, the database includes several user accounts that are automatically created upon installation. •In addition to the user accounts that you create, the database includes several user accounts that are automatically created upon installation. •All databases include the administrative accounts SYS, SYSTEM, SYSMAN. 7
• 8.
  Create new user/database •Thereare following steps for creating: •SQL>CREATE USER <username> IDENTIFIED BY <password> •SQL>GRANT DBA TO <username> •SQL>COMMIT; •SQL>EXIT; 8
• 9.
  Current User •Here isthe command to check which user is currently logged in. •SQL> SHOW USER 9
• 10.
  Tables/Relations/Entities •Let we havea small database named as uaar which deals with student, department ,subject and teacher. •We will perform all of our operations depending upon the defined structure as below: •Student (std_id , std_name , std_dept_id, d_o_b , age) •Teacher (tch_id ,tch_name , tch_qualification) •Department (dept_id , dept_name) •Subject (sub_id , sub_title , credit_hour) •Result (exam_id, sub_id, tch_id , total_marks , exam_date) •Sub_result (exam_id , std_id , obt_marks , percentage ) 10
• 11.
  ERD of UAAR 11
• 12.
  Data Definition 12
• 13.
  Create Table •We useSQL statement for creating/defining new table with “CREATE”keyword and following syntax: •SQL> CREATE TABLE <table_name> •( <column_name> <data_type>, •<column_name> <data_type>, •<column_name> <data_type> ); 13
• 14.
  Alter Table •In case,you have some new data definition to add or want to edit/delete existing data definitions use this SQL command with keyword “ALTER” and following syntax: •SQL> ALTER TABLE <table_name> ADD <column_name> <data_type>; •SQL> ALTER TABLE <table_name> DROP COLUMN <column_name>; •SQL> ALTER TABLE <table_name> MODIFY COLUMN <column_name> <data_type>; •*Note: Some database systems don’t allow to drop a column. 14
• 15.
  Rename Table •The RENAMEstatement is used to assign a new name to the existing table. •Rename statement has following syntax: •SQL> ALTER TABLE <table_name> RENAME TO <new_table_name>; 15
• 16.
  Drop/Delete Table •The DROPstatement is used to delete a table along with table structure,attributes and data. •Drop statement has following syntax: •SQL> DROP <table_name>; 16
• 17.
  Truncate Table •The TRUNCATEstatement is used to delete all data in a table . It does not delete table structure or attributes. •TRUNCATE statement has following syntax: •SQL> TRUNCATE <table_name>; 17
• 18.
  COMMENTS •Use the COMMENTstatement to add to the data dictionary a comment about a table or table column etc. •We use following syntax for creating comments: •COMMENT ON COLUMN <column_name> IS ‘<comment_statement>'; •We use following syntax to remove comments: •COMMENT ON COLUMN <column_name> IS ' '; 18
• 19.
  Your Suggestions? 19 “We allneed people who will give us feedback. That’s how we improve.” – Bill Gates Would like to hear at m.waheed3668@gmail.com