Introduction to SQL

SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It is used to perform tasks such as querying data, updating records, inserting data, and deleting records. Here are some key points about SQL:

  1. Data Querying: SQL allows users to query data from a database using the SELECT statement, which can retrieve specific data based on given criteria.

  2. Data Manipulation: SQL provides commands for inserting (INSERT), updating (UPDATE), and deleting (DELETE) data within database tables.

  3. Data Definition: SQL includes commands for defining the structure of databases and tables, such as CREATE TABLE, ALTER TABLE, and DROP TABLE.

  4. Data Control: SQL has features for controlling access to data within a database, including granting and revoking permissions using GRANT and REVOKE.

SQL is widely used in various database systems, including MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database.


Keep in Mind That...SQL keywords are NOT case sensitive: select is the same as SELECT

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Some of The Most Important SQL Commands
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
SELECT - extracts data from a table
UPDATE - updates data in a table
DELETE - deletes data from a table
INSERT INTO - inserts new data into a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

1.Data Definition Commands

Creating a Database

The CREATE DATABASE command is used to create a new database.

CREATE DATABASE database_name;

Altering a Database

The ALTER DATABASE command is used to modify a database. However, the specifics of what can be altered vary between different database management systems (DBMS). Common alterations include changing database settings or modifying properties.

Example: Renaming a Database (PostgreSQL)

ALTER DATABASE old_database_name RENAME TO new_database_name;

Example: Changing the Owner of a Database (PostgreSQL)

ALTER DATABASE database_name OWNER TO new_owner;

Dropping a Database

The DROP DATABASE command is used to delete an existing database. This action is irreversible and will remove all data and structures within the database.

DROP DATABASE database_name;

CREATE TABLE: Used to create a new table.

CREATE TABLE table_name 
( column1 datatype PRIMARY KEY, column2 datatype, column3 datatype );

ALTER TABLE: Used to modify an existing table (e.g., adding or dropping columns)

ALTER TABLE table_name ADD column_name datatype; 
ALTER TABLE table_name DROP COLUMN column_name;

DROP TABLE: Used to delete a table

DROP TABLE table_name;

CREATE INDEX: Used to create an index (search key).

CREATE INDEX index_name ON table_name (column1, column2);


2.Data Manipulation Commands

INSERT INTO: Used to insert new records into a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Used to modify existing records in a table.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE: Used to delete records from a table.

DELETE FROM table_name WHERE condition;

3.Data Querying Commands

SELECT: Used to query data from a database.

SELECT column1, column2 FROM table_name;

WHERE: Used to filter records.

SELECT column1, column2 FROM table_name WHERE condition;

ORDER BY: Used to sort the result set.

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

JOIN: Used to combine rows from two or more tables based on a related column.

SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;

3.Data Control Commands

GRANT: Used to give user access privileges to the database.

GRANT SELECT, INSERT ON table_name TO 'username';

REVOKE: Used to remove user access privileges.

REVOKE SELECT, INSERT ON table_name FROM 'username';

4.Transaction Control Commands

BEGIN TRANSACTION: Used to start a transaction.

BEGIN TRANSACTION;

COMMIT: Used to save the changes made by the transaction.

COMMIT;

ROLLBACK: Used to undo changes made by the transaction.

ROLLBACK;

These commands form the foundation of SQL and are essential for performing various operations in relational databases.



Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more