StatusNeo

A complete guide to SQL from scratch

Introduction to this blog

In this blog, we are going to learn about SQL(Structured Query language), DDL (Data Definition Language), and DML(Data Manipulation Language).

What is SQL(Structured Query Language)?

• SQL stands for the structured query language.
• It is a non-procedural language.
• Non-procedural language means the user has to specify only what to do and not how to do it.
• Non-procedural languages are also known as functional or applicative languages.
• So, in SQL user has to define only what he wants and not how to get it.
• In SQL, the block structure format of the English language is used.
• SQL has the following components-
  • DDL
  • DML
  • View Definition
  • Transaction Control
  • Embedded and Dynamic SQL
  • Integrity
  • Authorization
• The main work of SQL is to provide the accessibility and the manipulation of the database.
• SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987.
• SQL provides us the ability to-
  • Create a database
  • Delete a database
  • Create a view in a database
  • Alter the database
  • Create a table
  • Read the table
  • Alter and delete the table
  • Create and read records
  • Alter and delete records
  • Create different users and permissions on the database and tables
  • Perform different aggregate and joins on the records

What is RDBMS?

• RDBMS stands for the relational database management system.
• RDBMS is based on the relational model introduced by E.F. Codd.
• RDBMS consists of databases, where each database can have multiple tables.
• Every table consists of records or data entries.
• Each entry can be called a row, that consists of columns.
• The organized view of the dataset in the relational table is known as the logical view.
• No two identical entries can be made in a relational table.
• Rows are also called tuples.
• A column is also known as the field of the table.

• The above image is an example of a relational table, where FirstName, LastName, RollNumber, Course, FirstYear, and FinalYear are the columns and the rest are three rows(tuples).

Data Definition Language (DDL)

• DDL is a computer language that is used to create and alter database objects.
• Database objects can be views, schemas, tables, or indexes.
• It is also considered the subset of SQL.
• Create, alter, drop and truncate are the common DDL queries.
• The SQL DDL allows specification of not only a set of relations but also information about each relation, including-
  •  Schema for each relation in a database
  •  The domain of values associated with each attribute in. a table
  • The integrity constraints of a table
  • The set of indices to be maintained for each relation in a database
  • The security and authorization information for each relation in a database
  • The physical storage structure of each relation on the disk

Data Types in SQL

• Data types are also known as domain types.
• There are many different data types supported in SQL.
• Let’s have walk through them-
  • Char (n)- A fixed-length character length string with user-specified length.
  • Varchar (n)- A variable character length string with user-specified maximum length n.
  • Number (p, d)-A Fixed-point number with user-defined precision.
  • Real, double precision- Floating point and double-precision floating-point numbers with machine-dependent precision.
  • Float (n)- A floating-point number, with the precision of at least n digits.
  • Date- A calendar date containing a (four-digit) year, month, and day of the month.
  • Time- The time of day, in hours, minutes, and seconds Eg. Time ’12:30:45’.

Rules that are followed while writing a Query

1. Oracle reserved words cannot be used.
2. Underscore, numerals, and letters are allowed but not blank spaces.
3. The maximum length for the table name is 30 characters.
4. Two different tables should not have the same name.
5. Every column name must be unique.
6. Datatypes should be specified with proper length(width).
7. By default the values in a table are Null, we can add the Non-Null conditions.

SQL Queries(DDL Queries)

Create a table

Create table tablename (columnname datatype(size), columnname datatype(size));

Creating a table from a table

CREATE TABLE TABLENAME [(columnname, columnname, .........)]
AS SELECT columnname, columnname........FROM tablename;

Inserting Data into Table

INSERT INTO tablename [(columnname, columnname, .........)]
Values(expression, expression);

Inserting Data into Table from Another Table

INSERT INTO tablename
SELECT columnname, columnname, .......
FROM tablename;

Inserting Selected or filtered data into table from another table

INSERT INTO tablename
SELECT columnname, columnname........
FROM tablename
WHERE columnname= expression;

Retrieving(Viewing) of Data from Table

SELECT * FROM tablename;

Retrieving Specific Columns from the Table

SELECT  columnname, columnname, ....
FROM tablename;

Selecting Unique Values from the Table(Elimination of duplicates from the select statement)

SELECT DISTINCT columnname, columnname
FROM tablename;

Note- The SELECT DISTINCT * SQL syntax scans through entire rows, and eliminates rows that have exactly the same contents in each column.

SELECT DISTINCT *
FROM TableName;

Selecting a data set from table data

SELECT columnname, columnname
FROM tablename WHERE search condition;

Data Manipulation Language (DML)

• DML is a subset of SQL that is used to perform insert, delete and update the dataset.
• As the word manipulation suggests these queries are used to change the dataset or to update it.
• CRUD(Create, Read, Update and Delete) comes under DML.
• Data manipulation is-
  • The retrieval of information stored in the database(Select Operation).
  • The insertion of new information into the database(Insert Operation).
  • The deletion of information from the database(Delete Operation).
  • The modification of information stored by the appropriate data model. There are basically two types-
    • Procedural DML: require a user to specify what data are needed and how to get those data.
    • Non Procedural DML: require a user to specify what data are needed without specifying how to get those data.

SQL Queries(DML Queries)

Updating the content of a table

Update table name
Set columnname = expression, columnname =expression...... Where columnname = expression

Deletion Operation

Delete from table name;

Deletion of a specified number of rows

Delete from table name
where search condition;

Selecting Columns with Alias(Different Name)

Select columnname as result_columnname, columnname as result_columnname From tablename;

Logical Operators in SQL

• AND – All the conditions must be satisfied
• OR -Any one of the conditions should be satisfied
• NOT – No situation should be satisfied

Range Queries

• For range queries, we use between operation

SELECT  columnname, columnname ....
FROM tablename
WHERE columnname BETWEEN value1 AND value2;

Pattern Searching Queries

• “Like” the operator is used to do the pattern matching, it is one of the most commonly used operations on a string.
• The percent (%) symbol is used to do the pattern matching let’s take a look at how to use it-
  • ‘Karan %’ matches any string beginning with Karan
  • ‘% ego % matches any string containing “ego” as a substring
  • ‘ – – – ‘ matches any string with exactly three characters.
  • ‘ – – -% matches any string of at least three characters.

Order Queries

• The ‘order by’ clause is used to sort the table data according to one or more columns of the table.
• The table rows are ordered in ascending order of the column values by default. The keyword used for the same is ‘asc’. For sorting the table data according to columnName in descending order, the keyword ‘desc’ is used.

 select colname1, colname2,... from tablename where search condition order by colname1 asc/desc, colname2 asc/desc,...;

Delete VS Truncate Query

• The delete query is used to remove single or multiple tuples(records) from a table depending on the specified condition.
• The truncate query removes all the tuples(records) from the table but not the table itself. It preserves the table structure or schema.

Conclusion

• That’s it from this blog, in the next blog we will cover how to implement the restrictions and modifications on the structure of the table.
• Still Curious? Visit my website to know more!
• For more interesting Blogs Visit- Utkarsh Shukla Author
• Feature image credit- https://www.pexels.com/@utkarsh-shukla-204757820/
• Linked to part II- A complete guide to SQL from scratch- Part-II (Constraints)
• References- Internet