1 / 77

SQL Basics: Introduction to Databases, DBMS, & SQL Commands

Learn the fundamentals of databases, DBMS, and SQL commands like DDL, DML, and DCL. Understand relational databases, data types, table creation, data insertion, data retrieval, and data manipulation. Get insights into eliminating duplicates and sorting data.

wilberg
Download Presentation

SQL Basics: Introduction to Databases, DBMS, & SQL Commands

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Intorduction SQL Prepared BY: Mitali Sonar (Assistant Professor) Prepared By: Mitali Sonar (Assistant Professor)

  2. What is database? • Collection of information/ meaningful data • Ex:- postal address • Values in this fields are data • Address book is database Prepared By: Mitali Sonar (Assistant Professor)

  3. What is DBMS? • Software that designs, manages database • DBMS allows insert, update, delete and processing of data in database • Ex:- • Oracle • Ingres • Dbase • MS. Access Prepared By: Mitali Sonar (Assistant Professor)

  4. What is relational DBMS • DBMS based on relational model • Stores data in form of related data/tables • Dbase can Be viewed in many different ways • Ex:- • Oracle • Microsoft SQL server • Sybase SQL Server • DB2 • MySQL Prepared By: Mitali Sonar (Assistant Professor)

  5. DBMS Vs. RDBMS Prepared By: Mitali Sonar (Assistant Professor)

  6. Introduction to SQL • Structured Query Language • Provides interface to RDBMS • Language for communication with oracle server to access data • Category/ component of SQL • DDL commands (data definition language) • To create a database objects not data • Define relation schema ,deleting relations and modifying relational schemas • Ex:- • CREATE :- Used to create a db object • ALTER :- alter structure of DB • DROP:- Delete the object from DB • TRUNCATE :- Remove all records from DB Prepared By: Mitali Sonar (Assistant Professor)

  7. Introduction to SQL (conti…) • DML commands (Data Manipulation Language) • To manipulate data of a database objects • Includes commands to insert tuples into , delete tuples from and modify tuples in database. • INSERT :- Insert data into table • UPDATE:-Updates existing data within table • DELETE :- deletes all records from table • DQL command (Data Query Language) • To retrieve the data from a database • Getting data from database and imposing ordering upon it • SELECT:- retrieve data from the DB Prepared By: Mitali Sonar (Assistant Professor)

  8. Introduction to SQL (conti…) • DCL commands (Data Control Language) • Controlled access to data • GRANT - Used to give privilege to user on object • REVOKE - Used to withdraw the privilege that has been granted to the user. • COMMIT:- save work done • ROLLBACK :- restore the database to original state since last commit Prepared By: Mitali Sonar (Assistant Professor)

  9. Basic data type • char(size) • A fixed length character string with only 255 user specified characters • Data held is right padded with spaces to whatever size is specified • varchar2(size) • A variable character length string with alphanumeric values • maximum length 4000 characters • Inserted values are not contains white spaces • Date • a calendar date containing a year, month and day of the month • DD-MON-YYYY • Number (P,S) • Used to store a number (fixed or floating point). • Max precision can be 38 digits • P – max length of the data • S- number of places to the right of decimal Prepared By: Mitali Sonar (Assistant Professor)

  10. TABLE fundamentals • Table holds user data & Similar to a spreadsheet • Consist of rows (tuples) & columns (attribute) • Each column have data type associated with it. • Syntax :- CREATE TABLE table_name (col1 datatype(size), …., coln datatype(size) ); OUTPUT:- Prepared By: Mitali Sonar (Assistant Professor)

  11. Inserting data into tables • Loads the value passed by SQL insert command into columns specified • INSERT INTO tablename VALUES(<val 1>,<val 2>…….<valn>); Prepared By: Mitali Sonar (Assistant Professor)

  12. Viewing data in the tables • SELECT – is used to retrieve rows from one or more tables • All rows & all columns • SELECT * FROM tablename; • Ex: - show all employee details Prepared By: Mitali Sonar (Assistant Professor)

  13. Viewing data in the tables (cont..) • Selected columns & all rows • SELECT Column1, Column2 FROM tablename; Prepared By: Mitali Sonar (Assistant Professor)

  14. Viewing data in the tables (cont..) • Selected Rows and all Columns • SELECT * FROM tablename WHERE condition • Ex:- Display details of employee who lives in andheri area • Ex:- Display details of employee who live in vile parle area Prepared By: Mitali Sonar (Assistant Professor)

  15. Viewing data in the tables (cont..) • Selected Rows and selected Columns • SELECT Column1, Column2 FROM tablename WHERE condition • Ex:- list eno and ename who lives in vile parle area Prepared By: Mitali Sonar (Assistant Professor)

  16. Eliminate duplicate values • DISTINCT • It scans through the values of columns specified and display only unique values among them • SELECT DISTINCT column1 FROM tablename; Prepared By: Mitali Sonar (Assistant Professor)

  17. Sorting the data • ORDER BY :-retrieves rows from table either ascending or descending order • SELECT * FROM tablename ORDER BY colname (sortorder); • Ex:- show details of employee according to department name. Prepared By: Mitali Sonar (Assistant Professor)

  18. DELETE operation • Deletes rows from table that satisfies the condition provided by where clause • Removal of all rows form the table DELETE FROM tablename; • Removal of specific rows DELETE FROM tablename where Condition; Prepared By: Mitali Sonar (Assistant Professor)

  19. Update the content of table • Change or modify the data values in table • Updating all rows • UPDATE tablename SET col1 = expression1; • Ex:- update the department name to research for all employees Prepared By: Mitali Sonar (Assistant Professor)

  20. Update the content of table • Change or modify the data values in table • Updating rows conditionally • UPDATE tablename SET col1 = expression1 WHERE condition; • Ex:- update the address of employee sanket from sion to navi mumbai NEW TABLE OLD TABLE Prepared By: Mitali Sonar (Assistant Professor)

  21. Modifying the structure of the tables • ALTER TABLE :-Changes the structure of existing table • Add, delete columns / changes the datatype of columns /rename the columns or table itself • Adding a new column ALTER TABLE tablename ADD (new_column _name datatype(size)); Ex:- Enter a new field called contactno in table Prepared By: Mitali Sonar (Assistant Professor)

  22. Modifying the structure of the tables(cont..) • Dropping a column from a table ALTER TABLE tablename DROP COLUMN column name; Ex- drop the column contactno from the employee table Prepared By: Mitali Sonar (Assistant Professor)

  23. Modifying the structure of the tables(cont..) • Modifying Existing columns ALTER TABLE tablename MODIFY (colnname newdatatype (newsize)); Ex:- Change the employee name field to hold Maximum 30 characters Prepared By: Mitali Sonar (Assistant Professor)

  24. Restriction to ALTER TABLE • Following task can’t be performed using ALTER TABLE • Change the name of the table /columns • Decrease size of column • RENAME TABLES • REANAME tablename to newtablename; • Ex:- change the name of table to emp_data; • RENAME employee_data TO emp_data; Prepared By: Mitali Sonar (Assistant Professor)

  25. TRUNCATING TABLES • Empties a table completely • Different from delete in following ways • Drop and recreates the table that is much faster than deleting • Truncate are not transaction-safe • No. of deleted rows are not returned TRUNCATE TABLE tablename; • Ex:- clear all records from employee table • Output- • DESTROYING TABLES • When table becomes obsolete and needs to be discarded. • Destroys a specific table DROP TABLE tablename; • Ex:-DROP TABLE emp_data; • O/P :- table dropped. Prepared By: Mitali Sonar (Assistant Professor)

  26. Examining objects created by users • Finding out tables created by user • SELECT table_name FROM user_tables; • Displaying the table structure • To display information about columns defined in a table • DESCRIBE tablename; Prepared By: Mitali Sonar (Assistant Professor)

  27. Data constraints • Set of rules that must be applied to data prior to being stored in DB • Ex:- no employee in sales department should have salary more than 10,000 • If there is any violation between the constraint and the data action, the action is aborted by the constraint. • Once constraints is attached to table any SQL INSERT or UPDATE statement automatically causes these constraints to be applied to data before its being stored in table • 2 types • I/O constraint • Business rule constraints Prepared By: Mitali Sonar (Assistant Professor)

  28. Data constraints • In SQL, we have the following constraints: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT Prepared By: Mitali Sonar (Assistant Professor)

  29. I/O Constraint PRIMARY KEY CONSTRAINTS • One or more column in table that uniquely identify each tuple • No primary key column can contain NULL value • primary key column is a mandatory column • Data held across column must be unique. • Single column primary key is Simple key • A multi column primary key is a Composite primary key • Defined either in CREATE TABLE statement or in ALTER TABLE statement Prepared By: Mitali Sonar (Assistant Professor)

  30. PRIMARY KEY CONSTRAINTS • Syntax : Column-name datatype(size) primary key • Ex: CREATE TABLE Employee( ENO number(4) primary key, ENAME varchar2(20)); • PRIMARY KEY (column-name1, column-name2) • Ex:CREATE TABLE Employee( ENO number(4), ENAME varchar2(20), CONSTRAINT pk_ENO primary key (ENO)); • EX:- ALTER TABLE EmployeeADD CONSTRAINT pk_ENO primary key (ENO)); Prepared By: Mitali Sonar (Assistant Professor)

  31. Foreign key constraints • Represents relationship between two tables • Its value is derived from primary key of the other table • Table in which foreign key is defined is called foreign table • Table in which primary key is defined is called master table • Syntax :- REFERENCES tablename.columnname • Features • Parent that is being referenced has to be unique • Child may have duplicate • Parent record can be deleted provided no child exists • Master table cannot be updated if child record exists Prepared By: Mitali Sonar (Assistant Professor)

  32. Foreign key constraints • create table branch(branch_name char(15),branch_city char(30),assets number(12,2),primary key (branch_name )); • create table account(account_number char(10),branch_name char(15),balance integer,primary key (account_number), references branch (branch_name) ); Prepared By: Mitali Sonar (Assistant Professor)

  33. Foreign key constraint • Insert/update operations in foreign • A value cannot be inserted in foreign table if corresponding value is not presented in master table Prepared By: Mitali Sonar (Assistant Professor)

  34. Foreign key constraints • Delete operation on primary key table(master table) • Oracle display error message when deleting a record form master table and corresponding record exists in foreign key table • This will prevent the delete operation form execution Prepared By: Mitali Sonar (Assistant Professor)

  35. ON DELETE CASCADE • If a record is deleted in master table , all corresponding records in foreign key table is also deleted. • create table "branch" ( `"branch_name" char(15), "branch_city" char(30), "assets" number(12,2), constraint"branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE CASCADE ENABLE ) Prepared By: Mitali Sonar (Assistant Professor)

  36. Prepared By: Mitali Sonar (Assistant Professor)

  37. ON DELETE SET NULL • If a record is deleted from Master table, the value held by foreign key table is set to Null • The records in foreign key table will not deleted • create table "branch" ( "branch_name" char(15), "branch_city" char(30), "assets" number(12,2), constraint"branch_con" foreign key ("branch_name") references "account" ("branch_name") ON DELETE SET NULL) Prepared By: Mitali Sonar (Assistant Professor)

  38. Unique key constraint • It permits multiple entries of NULL into column • It will not allowed duplicate value • A table can have more than one unique key that is not possible for primary key • ALTER TABLE customerADD CONSTRAINT Cust_con UNIQUE (cust_no); Prepared By: Mitali Sonar (Assistant Professor)

  39. BUSINESS RULE CONSTRAINT • Rules are applied to data prior the data is being inserted • This will ensure that data in table have integrity • Ex;- Rule ” no employee in company shall get a salary less than 10000” • Inserted to column or table using create table or alter table • Constraints can be defined at • Column level • Table level Prepared By: Mitali Sonar (Assistant Professor)

  40. NULL VALUE CONCEPTS • Some fields do not have values because information is not available at time of data entry • Will place NULL value that is different from blank(if data type is character) or zero(if data type is number) • Null can be inserted in columns of any data type • If a column has NULL value than oracle ignore UNIQUE, FOREIGN KEY, CHECK constraints • NOT NULL concept • Ensures that column cannot left empty • Only be applied at column level • If column is defined as not null, that column becomes a mandatory column Prepared By: Mitali Sonar (Assistant Professor)

  41. CHECK CONSTRAINT • used to limit the value range that can be placed in a column. • Specified as logical expression that evaluates as TRUE or FALSE • CHECK constraint on a single column • allows only certain values for this column. • CHECK constraint on a table • can limit the values in certain columns based on values in other columns in the row. • CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),CONSTRAINT chk_Person CHECK (P_Id>0 and City=‘Ahmedabad')) Prepared By: Mitali Sonar (Assistant Professor)

  42. Create table customer_master ( cust_no varchar2(5), Fname varchar2(10), Lname varchar2(10), CHECK (cust_no like ‘C%’), CHECK (Fname =UPPER (FNAME)) ); If condition has false value error message will be displayed and processing stops there Prepared By: Mitali Sonar (Assistant Professor)

  43. Default Constraint • used to insert a default value into a column. • The default value will be added to all new records, • if no other value is specified. • CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) DEFAULT ‘ahmedabad'); Prepared By: Mitali Sonar (Assistant Professor)

  44. Create the tables described as follow • Table name: Product Master Prepared By: Mitali Sonar (Assistant Professor)

  45. Computations done on table data • manipulates individual data items and returns a result. • Ex:- display all employee’s detail with their annual salary (salary *12) • Arithmetic operators Prepared By: Mitali Sonar (Assistant Professor)

  46. Customer Table SELECT cname, opening_amt, receive_amt, (opening_amt + receive_amt)   FROM customer   WHERE (opening_amt + receive_amt)>15000;  Prepared By: Mitali Sonar (Assistant Professor)

  47. Renaming the column used with expression • SQL aliases are used to give a column in a table, a temporary name. • SELECT column_name AS alias_name FROM table_name; Prepared By: Mitali Sonar (Assistant Professor)

  48. SELECT C_Name, Address+City+PostalCode+CountryAS AddressFROM Customers; Prepared By: Mitali Sonar (Assistant Professor)

  49. Logical operators • Logical operators in SQL will return either true or false value. Prepared By: Mitali Sonar (Assistant Professor)

  50. "OR" Logical Operator: • find the details of students who are studying either Maths or Science, the query would be like, • SELECT * FROM studentdetails WHERE sub1 = 'Maths' OR sub1= 'Science' Prepared By: Mitali Sonar (Assistant Professor)

More Related