basic SQL script

A basic SQL script is a set of SQL commands used to create, manipulate, or retrieve data from a database. For example:

-- Create a database
CREATE DATABASE SampleDB;

-- Use the database
USE SampleDB;

-- Create a table
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username NVARCHAR(50),
    Email NVARCHAR(100)
);

-- Insert data
INSERT INTO Users VALUES
(1, 'Alice', '[email protected]'),
(2, 'Bob', '[email protected]');

-- Retrieve data
SELECT * FROM Users;

SQL Statement:

An SQL statement is a single SQL command to perform a specific operation. Examples include:

-- DDL (Data Definition Language):

CREATE TABLE Employees (ID INT, Name NVARCHAR(50));

--DML (Data Manipulation Language):

INSERT INTO Employees VALUES (1, 'John Doe');

--DCL (Data Control Language):

GRANT SELECT ON Employees TO user1;

DDL in SQL (Data Definition Language):

DDL is used to define the structure of a database, such as creating, altering, or dropping tables.

-- Create a table
CREATE TABLE Products (ID INT, Name NVARCHAR(50));

-- Alter a table
ALTER TABLE Products ADD Price DECIMAL(10, 2);

-- Drop a table
DROP TABLE Products;

DCL in SQL (Data Control Language) :

DCL is used to control access to data in a database by granting or revoking permissions.

-- Grant permissions
GRANT SELECT, INSERT ON Users TO user1;

-- Revoke permissions
REVOKE INSERT ON Users FROM user1;

DML in SQL (Data Manipulation Language):

DML is used to manipulate data stored in tables, such as inserting, updating, deleting, or retrieving data.

-- Insert data
INSERT INTO Users (UserID, Username, Email)
VALUES (3, 'Charlie', '[email protected]');

-- Update data
UPDATE Users SET Email = '[email protected]' WHERE UserID = 1;

-- Delete data
DELETE FROM Users WHERE UserID = 2;

-- Retrieve data
SELECT * FROM Users;

Views in SQL:

A view is a virtual table based on a query. It does not store data itself but retrieves data from the underlying tables.

• Create a View:

  CREATE VIEW ActiveUsers AS
  SELECT UserID, Username, Email
  FROM Users
  WHERE IsActive = 1;
  

• Query a View:

  SELECT * FROM ActiveUsers;
  

Stored Procedure

A stored procedure is a precompiled SQL code block that can be executed repeatedly. It can include control logic (loops, conditions) and accept parameters.

• Create a Stored Procedure :

  CREATE PROCEDURE GetUserDetails
  @UserID INT
  AS
  BEGIN
      SELECT * FROM Users WHERE UserID = @UserID;
  END;
  

• Execute the Stored Procedure:

  EXEC GetUserDetails @UserID = 1;