SQL.pptx Data Types Database Operations Types

Feel free to ask any questions after the session. If a topic requires a more detailed explanation, we’ll cover it during the dedicated Q&A session

Installing Oracle Database ● To install Oracle database on your computer, you need to download the installer from the Oracle website. ● To interact with Oracle Database we can use below tools ● TOAD ● SQL Developer ● PL/SQL Developer Webtool: https://livesql.oracle.com/apex/f?p=590:1000

What is a Database? ●A database is a set of data stored in a computer. This data is usually structured in a way that makes the data easily accessible. ●A Database is a collection of information that is well organized so that it can be easily accessed ,managed and updated. ●Database Management system should provide systematics method of 1.Creating Database 2.Updating the database 3.Storing the database 4.Retrieving of data from Database. CREATE DATABASE my_database;

What is Data ● Data is a collection of facts, such as numbers, words, measurements, observations or just descriptions of things. ● Data is any sort of information which is stored in computer memory. This information can later be used for a website, an application or can be used in future. ● Data can be structured OR unstructured. ● Structured-Student Name, Address ● Unstructured-Student Photo,AddrMap ● If you create a notepad file, then the content of that document is data. ● It can simply be a piece of information, a list of grocery items, or observations, a story or a description of a certain scenario.

Metadata ●Metadata is a data about data. Metadata shows basic information about data, which can make finding and working with specific instances of data easier. ●Metadata describes relevant information about the data. ●It is stored in data dictionary. ●Metadata refers to name of attributes, their types, user constraints, integrity information and storage information. ●if you create a notepad file the name of the file, storage description, type of file, size of file all becomes metadata of your file. ●Metadata properties: Data Name,Definitions,Length or Size,Values allowed,Source of data,Ownership.

In sql rows is called as tuples

Connecting to the Database Instance ● A connection is a communication pathway between a user process and an Oracle Database instance. ● A session represents the state of a current user login to the database instance. For example, when a user starts SQL*Plus, the user must provide a valid username and password, and then a session is established for that user. Connections and sessions are closely related to user processes but are very different in meaning.

Example of database Release Number

Tables: Rows and Columns • The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data. • A table is the simplest example of data storage in RDBMS. ID Name AGE COURSE 1 Ajeet 24 B.Tech 2 aryan 20 C.A 3 Mahesh 21 BCA 4 Ratan 22 MCA 5 Vimal 26 BSC

What is SQL? • SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret. • It’s used to perform data interacting tasks such as: Creating a table and inserting data into it. Querying and filtering data. Modifying and updating data. Delete data or table.

Desc, Information, Info + • Describe table_name; • Desc table_name;

SQL Case Sensitve select * from employees; SELECT * FROM EMPLOYEES; Select * From Employees SelecT * fROm eMpLoyees;

You can write SQL query in multiple lines. Multiple space Keywords cannot be abbreviated or split SQL whitespace , Semi Column, Slash: sql can be terminated by a semi colon ';' or a forward slash "/" sign . "Need at least one space between keywords."

When cause and action will come is mention in below example Error Msg : 1.SELECT * employees; 2.SELECT 1 + 'abc' FROM dual; 3.SELECT 1 + 2 FROM dual; 4.CREATE OR REPLACE PUBLIC SYNONYM jobs FOR hr.jobs;

DDL • DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. • CREATE – Creates objects in the database ALTER – Alters objects of the database DROP – Deletes objects of the database TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DML • DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. • SELECT – Retrieves data from a table INSERT - Inserts data into a table UPDATE – Updates existing data into a table DELETE – Deletes all records from a table

DCL • DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. • GRANT – Gives user's access privileges to database REVOKE – Withdraws user's access privileges to database given with the GRANT command GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: dbo to SqlUser

TCL • TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database. • COMMIT – Saves work done in transactions ROLLBACK – Restores database to original state since the last COMMIT command in transactions SAVE TRANSACTION – Sets a savepoint within a transaction

DQL Data Query Language (DQL) is used to fetch the data from the database.

DQL Results/ Result Set If 100 records we will get 100 records. If 1000 records we get 1000 records; * Is used to retrieve all columns

Query • It is an operation that retrieves data from one or more tables or views • Select statement is used to Retrieves data from one or more tables/views/Mviews. Synatax: SELECT [COLUMNS] FROM [Table_Name] WHERE [Condtions] GROUP BY ORDER BY 1. SELECT * FROM employees; 2. SELECT * FROM employees WHERE salary > 10000; 3. SELECT * FROM employees WHERE job_id = 'IT_ROG';

SQL Aliases • SQL aliases are used to give a table, or a column in a table, a temporary name. • Aliases are often used to make column names more readable. • An alias only exists for the duration of that query. • An alias is created with the AS keyword. • SELECT column_name AS alias_name FROM table_name; • SELECT column_name AS ”new name” FROM table_name; • select * from dual; • select 'My Name is Adam' as "Output" from dual; • select 'I''m using quote operator in SQL statements' as "Output" from dual; • select q'[I'm using quote operator in SQL statements]' as "Quote Operator" from dual; • select q'<I'm using quote operator in SQL >' as "Quote Operator" from dual; • select q'dI'm using quote operator in statementsd' as "Quote Operator" from dual;

A constraint in SQL is a rule applied to a column or a table to enforce data integrity and consistency in the database. Constraints are used to ensure that the data in the database adheres to certain criteria, such as uniqueness, non-nullability, or referential integrity.If an INSERT, UPDATE, or DELETE operation violates a constraint rule, the database rejects the operation and raises an error.

NOT NULL: Ensures that a column cannot have a NULL value. UNIQUE: Ensures that all values in a column are unique. PRIMARY KEY: Combines NOT NULL and UNIQUE to uniquely identify each record in a table. FOREIGN KEY: Maintains referential integrity by linking columns in two tables. CHECK: Ensures that all values in a column satisfy a specific condition. DEFAULT: Sets a default value for a column when no value is specified.

UNIQUE ● Ensures all values in a column or set of columns are unique. ● Unlike the primary key, a table can have multiple unique constraints. CHECK (MySQL 8.0+) ● Ensures all values in a column satisfy a specific condition. DEFAULT ● Assigns a default value to a column when no specific value is provided. NOT NULL ● Prevents null values from being entered in a column, ensuring a column always has a value. ● Commonly used with primary keys and essential columns

What is a Primary Key? ● A primary key is a unique identifier for each record in a table. ● It ensures uniqueness and non-nullability of the data in a particular column (or a set of columns). ● No two rows in a table can have the same primary key value. CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), age CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), age INT );INT );

What is a Foreign Key? ● A foreign key is a column or a set of columns in one table that references the primary key of another table. ● It establishes a relationship between two tables, enforcing referential integrity by linking records across tables. CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ); CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) );

ALTER TABLE table_name ADD PRIMARY KEY (column_name); ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name); ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column); ALTER TABLE table_name DROP PRIMARY KEY; ALTER TABLE table_name ADD CONSTRAINT pk_constraint_name PRIMARY KEY (column_name); ALTER TABLE table_name ADD CONSTRAINT unique_constraint_name UNIQUE (column_name); ALTER TABLE table_name ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column); ALTER TABLE table_name ADD CONSTRAINT chk_constraint_name CHECK (condition); ALTER TABLE table_name DROP PRIMARY KEY; ALTER TABLE table_name DROP INDEX unique_constraint_name; ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name; ALTER TABLE table_name DROP CHECK chk_constraint_name; SELECT CONSTRAINT_NAME, TABLE_NAME FROM information_schema.table_constraints WHERE table_name = 'your_table_name';

select * from products where productname=Laptop; select * from products where Category=Furniture;

● NULL means "no value" or "unknown." ● It is not the same as zero (0), an empty string (""), or a space. ● NULL is used when a value is missing, unknown, or not applicable. A NULL value represents missing or undefined data.Something being empty, missing, or not applicable.In MySQL, NULL represents missing, unknown, or undefined data. It is not the same as zero (0) or an empty string (''). Comparisons using = or != with NULL return FALSE; instead, use IS NULL or IS NOT NULL. Functions like COALESCE() and IFNULL() help handle NULL values effectively.

Where Condition Introduction to WHERE Clause: ● The WHERE clause is used to filter records in an SQL query. ● It allows us to specify conditions, returning only the rows that meet those criteria. ● Often used with SELECT, UPDATE, DELETE, and JOIN statements. SELECT column1, column2, ... FROM table_name WHERE condition;

Table: Products ● Columns: ProductID, ProductName, Category, Price, Stock Table: Sales ● Columns: SaleID, ProductID, QuantitySold, SaleDate, CustomerRegion Table: Customer ● Columns: CustomerID, CustomerName, Phone, Email, Address

Filtering : Operators in WHERE Clause: ● Comparison Operators: =, !=, <, >, <=, >= ● Pattern Matching: IN,LIKE ● Null Check: IS NULL, IS NOT NULL ● Range Check: BETWEEN ● Logical Operators: AND(a>1 and a <10), OR(a>1 or a <10), NOT IN ● Arithmetic Operators: +, -, *, /, % ● Other Operators: distinct

• Find all products priced exactly 200 ₹ . • Identify sales with quantities not equal to 10: • List all sales where the quantity sold is less than 10. • Find products in stock and priced below $100 • Retrieve customers whose names contain the letter "a". • Find products that belong to the "Electronics" category. • List customers from regions starting with "N": • Get products priced between 50 and 150 ₹ ₹ . • Show sales made between January 20 and January 22, 2024. • Find customers where the email address is missing (if any). • Check for products with no stock • Identify sales records where the CustomerRegion is provided. • Retrieve all sales made between January 15, 2024, and January 20, 2024, but exclude sales where the CustomerRegion is North. Find products that are in the "Furniture" category, priced between 100 and ₹ 400 ₹ , and have stock greater than 20.

• List all products that are in stock and priced below 100. ₹ • Retrieve customers who live in either "North" or "South" region. • Calculate the total stock value for products by multiplying Price by Stock. • Find products where the stock is a multiple of 10. • List sales from either "North", North East or "South" region: • Get a distinct list of all product categories. • Find distinct regions from the Sales table. • Combine simple operations. Find all products in the "Electronics" category priced between 100 and 500 ₹ ₹ and currently in stock.

SQL.pptx Data Types Database Operations Types

More Related Content

Similar to SQL.pptx Data Types Database Operations Types

Recently uploaded

SQL.pptx Data Types Database Operations Types