Introduction to SQL: Basics for Beginners

For those of you who don’t know or are simply new to the term & concept of SQL, it stands for “Structured Query Language.” It’s a relatively simple language for coding, but it’s incredibly powerful. You can use SQL statements to get specific data from databases, update databases, and more.

SQL is used by many relational database management systems, such as MySQL, Oracle, and Microsoft SQL Server. Sybase, Ingres, and others also use SQL.

SQL is a highly sought-after language for data analysts. But it’s also useful for financial teams, programmers, non-technical people, and even marketers, sales reps, and other professionals who need to extract data from large databases. You don’t have to be an SQL all-star to use it, but knowing the basics will help you ask better questions (at least).

What SQL can do?

SQL is used to communicate with Relational Databases. Below are the following  effects that SQL can do:

• Execute the queries in the Database to fetch the data.
  
  
• SQL can define the structure of the Database.
  
  
• SQL can manipulate the data.
  
  
• SQL can Create, Drop, Alter, and Truncate the table or Database.
  
  
• SQL can handle the Database that can be used in the following ways to Insert, update, and Delete the data from the Database.
  
  
• SQL is also used to Grant and Revoke authorization for someone to pierce the Database.
  
  

Note: Tables are the objects that store the data in Row and Column format.

Databases in SQL

The database is an organizational form to store the data in table format. Databases are further divided into two types:

• Relational Database: Every piece of data in this database is connected to. That's because each data value has its own unique identity, like a record. All the data is stored in this model, so each line of data is linked to another line using a primary key, and each table is linked to another table using a different key.
  
  
  

• Non-Relational Database (NoSQL): NoSQL is a type of database that stores and retrieves data in a different way than relational databases. It's designed to be easier to use, scale up to multiple computers, and control availability. It also uses different data structures than relational databases, which can speed up some operations. Mongo DB is a document-based NoSQL database that can be used for a variety of tasks.

Comment in SQL

Comment is used to write any note that will help other developers know the purpose of the code. The comment is only for reading purposes whenever we run the script, it will never execute.

• -- This double hyphen is used to comment on any single line in the SQL.
  
  

  SELECT * FROM Customers -- This is a comment

• # Hash is also used to comment on a single line.
  
  

  SELECT * FROM Customers # This is a comment

• /* */This is used to comment multiple lines.
  
  

  /* This is a multi-line comment. It can span multiple lines. */ SELECT * FROM Orders

Data Type in SQL

1. String: Char, Varchar, Binary, VarBinary, TinyBlob, TinyText, Text, Blob Etc. These are the types of string data types in SQL.
   
   
2. Integer: Int, Integer, Small Int, Boolean, Float, Big Int, Double, Decimal Etc.
   
   
3. Data and Time: Date, Timestamp, Year, Time etc.

Types of Commands in SQL

The below figure shows the different types of commands that are used in SQL.




1) DDL: (Data Definition Language) used to create and manipulate the structure of the schema or database.

• Create: This command is used to create the database and table.
  
  

  CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), hire_date DATE );

• Alter: This is used to change and modify the data type or column name.
  
  

  ALTER TABLE employees ADD email VARCHAR(100); ALTER TABLE employees MODIFY hire_date DATETIME; ALTER TABLE employees DROP COLUMN email;

• Drop: This command is used to drop the database and table.
  
  

  DROP TABLE employees;

• Truncate: This command is used to delete the data from the existing table but the data structure remains in the database.
  
  

  TRUNCATE TABLE table_name;

2) DML: (Data Manipulation Language) These commands are used to manipulate the data.

• Insert: Insert is used to insert the data in the database.
  
  

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

• Update: UPDATE is used to update the record.
  
  

  UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE condition;

  
  

Note: The ‘WHERE’ condition is a must while using the update command.

• Delete: DELETE is used to delete any records, but we have to use WHERE if we don’t use the where clause in delete it will delete all the records from the table.
  
  

  DELETE FROM table_name WHERE condition;

  
  

Note: WHERE condition is a must while using the DELETE command.

3) DQL: (Data Query Language) These commands are used to start the query.

• Select: Select is used to start writing the query to fetch the data.
  
  

  SELECT column1, column2 FROM table_name WHERE condition;

4) DCL: (Data Control Language) These commands are used to grant and revoke the permissions to the person from the database.

• Grant: This command gives the user to access the database.
  
  

  GRANT privilege(s) ON object TO user_or_role;

  
  

• Revoke: This command revokes the user to access the database.
  
  

  REVOKE privilege(s) ON object FROM user_or_role;

5) TCL: (Transaction Control Language) These commands are used to deal with Transactions within the database.

• Commit: This command is used to save the transaction within the database permanently.
  
  

  COMMIT;

• Rollback: This command is used to roll back the transaction till the previously committed statement.
  
  

  ROLLBACK;

  
  

• Savepoint: This command is used to save the data at a point temporarily.
  
  

  SAVEPOINT savepoint_name;

  
  

In conclusion, the commands that are used in SQL are the building blocks to write the syntax and are very basic things to fetch the data from the database.

Types of Keys in SQL

Keys are used to identify the Tuple/Record uniquely.

1) Candidate Key: Candidate Key is the column or group of columns that are used to identify the record uniquely.

2) Primary Key: The Primary Key is a column or group of columns that are used to identify the record uniquely. These are selected from the candidate Key.

3) Alternate Key: All columns in the candidate key are Alternate Key except the Primary Key.

4) Foreign Key: A Foreign Key is a column or set of columns that refer to the Primary Key of another table.

5) Unique Key: The unique Key is the same as the Primary Key, but it accepts only one null value.

In SQL, keys are the linchpins of database integrity and relationships. They ensure data uniqueness and enable efficient data retrieval, making them vital in database design. Mastering key concepts is essential for building robust and reliable database systems.

Constraints in SQL

Constraints are a set of rules that are applied to the columns to maintain Data integrity.

• Primary Key Constraint: This constraint is used to find the record uniquely, and it can’t contain a Null () value.
  
  

  CREATE TABLE students ( student_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );

  
  
• Foreign Key Constraint: This Constraint is used to establish the relation between two tables to make referential integrity.
  
  

  CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, -- Foreign key referencing the customer table FOREIGN KEY (customer_id) REFERENCES customers (customer_id) );

  
  
• Unique Constraint: Unique is the same as the primary key, but the difference is that it can contain one Null value.
  
  

  CREATE TABLE products ( product_id INT UNIQUE, product_name VARCHAR(100) );

  
  
• Check Constraint: Check Constraint checks whether the condition should be met to make data valid.
  
  

  CREATE TABLE employees ( employee_id INT, hire_date DATE, CHECK (hire_date >= '2000-01-01') );

  
  
• Default Constraint: This constraint ensures that if any value goes null in the column where Default is used, it will automatically consider the null value as the Default value.
  
  

  CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE DEFAULT GETDATE() );

  
  
• Not Null Constraint: This constraint ensures that the columns don’t contain any Null value while inserting the data.
  
  

  CREATE TABLE customers ( customer_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL );

Constraints in SQL are the safeguards that ensure data delicacy and integrity within a database. They play a critical part in maintaining the trustability of data and administering business rules.

Clauses in SQL

Clauses are the components of the SQL queries to manipulate, modify, and fetch the data from the Database.

1) Select: Select Clause is used to fetch the data from the database.

2) From: From Clause Specifies which table we use to retrieve the data.

3) Where: Where Clause is used to filter the data based on the condition.

4) Group By: Group By Clause is used to group the same data row-wise.

Note: Group by is used with Aggregate functions

5) Having: Having Clause is used to filter the data retrieved through grouping the data.

6) Order By: Order By Clause is used to sort the data in ascending or descending order.

Note: The order in ORDER BY is always ascending. (desc) is used for descending order

7) Limit/Offset: This clause is used to limit the no. of rows to fetch the data.

SQL clauses are the essential elements that shape database queries and operations. They provide flexibility and control, allowing for precise data retrieval, filtering, and modification. Understanding SQL clauses is fundamental for harnessing the full potential of relational databases.

Conclusion

In conclusion, with the basics of SQL, you will be able to communicate with relational databases. It also provides you with a base to work with relational databases. This composition contains the introductory commands of SQL, which is a structure block to your SQL proficiency.

Through this composition, you will be able to get the knowledge to create, manipulate, insert, retrieve, update, and delete in the database. These skills are very precious for anyone who is involved in data management, data analytics, and more.