Getting Started: Basics of SQL for Data Management

Why SQL?

In today's data-centric environment, businesses depend significantly on data to drive informed decision-making. SQL is a robust tool that empowers users to interact with and manipulate databases. It enables access to data in relational database management systems, allows for the description and definition of data within a database, and facilitates data manipulation. SQL can be embedded within other languages using modules, libraries, and pre-compilers. It also provides capabilities for creating and dropping databases and tables, as well as creating views, stored procedures, and functions. Additionally, SQL allows users to set permissions on tables, procedures, and views.

What is SQL?

SQL, Structured?Query?Language, is a standardized programming language designed to manipulate and manage data stored in relational databases. It allows users to perform various operations, such as querying data, updating records, and managing database structures.

SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:

• DDL -Data Definition Language

CREATE-Creates a named database schema, a new table, a view of a table, or other object in the database.

-- Creates a new table named CUSTOMERS
CREATE TABLE CUSTOMERS (?
ID INT  NOT NULL,  
NAME  VARCHAR(20)  NOT NULL,  
AGE INT NOT NULL,
ADDRESS VARCHAR(20)  NOT NULL
SALARY INT NOT NULL);        

ALTER-Modifies an existing database object, such as a table.

-- Modifies the table CUSTOMERS, adding a new column
ALTER TABLE CUSTOMERS
ADD COLUMN EMAIL VARCHAR(50);        

DROP-Deletes an entire table, a view of a table or other object in the database.

-- Deletes the table CUSTOMERS
DROP TABLE CUSTOMERS;        

TRUNCATE- Deletes the table quickly and recreates it with the same schema. TRUNCATE cannot be rolled back.

TRUNCATE TABLE CUSTOMERS;        

• DML -Data Manipulation Language:

SELECT- Retrieves certain records from one or more tables.

-- Retrieves all records from the CUSTOMERS table
SELECT * FROM CUSTOMERS;        

INSERT- Creates a record

-- Inserts a new record into the CUSTOMERS table
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'John ', 23, 'Texas', 50000);        

UPDATE-Modifies records

-- Modifies the SALARY of a record in the CUSTOMERS table
UPDATE CUSTOMERS
SET SALARY = 60000
WHERE ID = 1;        

DELETE-Deletes records

-- Deletes a record from the CUSTOMERS table
DELETE FROM CUSTOMERS
WHERE ID = 1;        

• DCL -Data Control Language:

GRANT-Gives a privilege to user

-- Grants SELECT privilege on the CUSTOMERS table to a user
GRANT SELECT ON CUSTOMERS TO username;        

REVOKE-Takes back privileges granted from user

-- Revokes SELECT privilege on the CUSTOMERS table from a user
REVOKE SELECT ON CUSTOMERS FROM username;        

What is RDBMS?

RDBMS stands for 'Relational Database Management System'. RDBMS is the basis for SQL and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model.

What is table?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows. The following CUSTOMERS table is an example of the simplest form of data storage in a relational database:

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record or row?

A record, also called a row of data, is each individual entry that exists in a table. For example, there are 6 records in the above CUSTOMERS table. A record is a horizontal entity in a table.

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table. For example, a column in the CUSTOMERS table is ADDRESS which represents location.

SQL Constraints:

Constraints are rules applied to data columns in a table to restrict the type of data that can be stored. They ensure data accuracy and reliability in the database. Constraints can be either column-level, applied to a single column, or table-level, applied to the entire table.

Here are some commonly used constraints in SQL:

• PRIMARY KEY: Uniquely identifies each row/record in a database table and does not contain a NULL value.

CREATE TABLE employee(
id INT PRIMARY KEY,
name VARCHAR (100);        

• FOREIGN KEY: Uniquely identifies rows/records in another database table. It ensures the referential integrity of the data in one table to match values in another table.

CREATE TABLE department(
departmentID INT PRIMARY KEY,
departmentName VARCHAR(100);

CREATE TABLE employee(
id INT PRIMARY KEY,
name VARCHAR(100),
departmentID INT,
FOREIGN KEY (departmentID) REFERENCES department(departmentID);        

• NOT NULL Constraint: Ensures a column cannot have a NULL value.

CREATE TABLE employees(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL);        

• DEFAULT Constraint: Assigns a default value to a column when no value is specified.

CREATE TABLE employee(
id INT PRIMARY KEY,
name VARCHAR(50),
status VARCHAR(20) DEFAULT 'Active');        

• UNIQUE Constraint: Ensures all values in a column are unique.

CREATE TABLE employee(
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE);        

• CHECK Constraint: Ensures all values in a column meet certain conditions.

CREATE TABLE employee(
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
CHECK (age>=20));        

Getting Started with SQL

To begin learning SQL, we can explore a variety of online resources, tutorials, and courses. Practice is essential for mastering SQL, so try using free tools like MySQL Workbench or SQLite to create and manipulate our own databases. Additionally, many online platforms such as W3 schools, Udemy, Coursera, Codeacademy provide interactive SQL exercises to reinforce learning.

Conclusion

SQL is the backbone of data management, allowing users to effectively interact with and manipulate relational databases. Its versatility, efficiency, and standardization make it an essential tool for data professionals. By learning SQL, one can unlock the potential of data, facilitate informed decision-making, and advance career in the dynamic field of data science. SQL offers the foundational skills needed for success in data management and analysis.

?

?