Open in app

Sign in

Write

Sign in

Introduction to SQL -A Brief Guide to Get Started.

Alfiya Siddique
8 min readMar 31, 2023

--

All you need to know about the basics of SQL and MySQL

Introduction To Database and Database Management Systems.

A database is a collection of related data and a Database Management System is software that manages data. Database Management System is abbreviated as DBMS. DBMS carries out all the operations related to data. There are mainly two types of databases. NoSQL (Not Only SQL) and SQL (Structured Query Language).

  • SQL: Structured Query Language (SQL) is a type of database that contains relations. A relation is normally a table with rows and columns. Hence it is also called as a relational Database. Here, the data is stored in the form of relations or groups of relations. Some of the SQL DBMSs are MySQL, Oracle, Postgre SQL, SQLite, and many others. Data is stored in the following manner in an SQL database:

In this article, we are going to learn about the following:

  • Terminologies related to SQL.
  • SQL Data Types.
  • MySql Installation.
  • SQL Commands.

Terminologies related to SQL.

The following terms are used in a relational database.

  • Relation- A table is termed as relation in SQL.
  • Attribute- A column in a table is called an attribute.
  • Tuple- A record or a row of a table is called a tuple in SQL.
  • Domain- A domain contains a set of atomic values that an attribute can accept.
  • Schema- A fixed structure for the database records is defined as schema. If the record does not meet the schema requirements then it will not be inserted.

Data Types in SQL.

Data types are used to represent the nature of the data that can be stored in a database table. The data type is assigned to the attributes so that the attribute can accept the correct type of data. Following are the different types of data types in SQL:

  • char(n)- It is a fixed-width character string data type. Its size is up to 8000 characters.
  • varchar(n)- It is a variable-width character string data type. Its size is up to 65,535 characters.
  • bit- Accepted data in this type are 0, 1, or null.
  • int- Allows the number of Integers.
  • money- It is used for monetary data.

Installation of MySQL.

To run SQL commands you need to set up the environment in your machine. Follow this guide to install MySQL and MySql workbench on your machine.

Open the MySql Workbench application and you will get the interface as shown below:

Click on the Local Instance MySQL80 and enter the password that you have set while installing MySql Database. Once you logged in you will get an interface as below:

The element with a red border is the editor where you can write commands and the one with a green border is used to run those commands.

SQL Commands.

SQL commands are the statements or instructions that are used to communicate with the SQL database. SQL commands are also known as queries. SQL queries are not case-sensitive but it is good practice to write SQL keywords in uppercase and user-defined names in lowercase. Note that the syntax of SQL commands may differ slightly in different DBMSs.

SQL queries are divided into the following:

DDL- Data Definition Language.

DDL consists of SQL commands that can be used to define or create the database and table. It simply deals with the schema of the table. The commands in this group are related for create operations.

  • Create Database Command:
    The create command is used to create a table or database, the example and syntax for this command are as follows:
/*Syntax for Databse Creation - CREATE DATABASE databaseName */
CREATE DATABASE myDB;  --Creates a database name mydb

In the above example, CREATE and DATABASE are keywords and mydb is the database name.

  • Create Table Command:

This command creates table and the syntax is as follows:

/*Syntax for Table Creation - 
CREATE TABLE tablename(
 col1Name DATATYPE CONSTRAINTS;
 col2Name DATATYPE;
         .
         .
colnName DATATYPE;
 )
-- Note: Contraints are optional in above syntax
*/
CREATE TABLE user(
 id INT PRIMARY KEY,
 fName VARCHAR(45) NOT NULL,
 lName VARCHAR(45) NOT NULL,
 age INT NOT NULL,
 occupation VARCHAR(50) NOT NULL,
);

In the above example, CREATE and TABLE are keywords. INT and VARCHAR(n) are reserved words for the data type, INT means integer and varchar(n) is for string, we can also provide max string length in the curly braces.PRIMARY KEY and NOT NULL are constraints.

Constraints:

Constraints are set of rules or restrictions that are enforced on the attributes of the tables to maintain accuracy and consistency. It is used to ensure the correctness of data stored in a database. They are used while defining a table schema. The following are the different types of constraints:

1. Not Null- This constraint makes sure that some value is mandatory to be stored. If the value is not given for a mandatory attribute then it will return an error.

-- Example
CREATE TABLE Persons (
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
)

2. Default- Default constraint specifies a default value for a column if no value is given while inserting a tuple.

-- Example
CREATE TABLE Persons (
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    country VARCHAR(255) DEFAULT 'India',
)

3. Unique- It ensures that a column will have unique values for rows in a relation.

-- Example
CREATE TABLE Persons (
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    country VARCHAR(255) DEFAULT 'India',
    phoneNumber VARCHAR(10) UNIQUE,
)

4. Check- It is used to specify the range of values that can be inserted in the column.

-- Example
CREATE TABLE Persons (
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    country VARCHAR(255) DEFAULT 'India',
    phoneNumber VARCHAR(10) UNIQUE,
    age INT CHECK(age >= 18)
)

Using check constraint we can also validate the format of a string, as shown below:

In the below example, CONSTRAINT ‘email-check’ is the name of the constraint, and email is the attribute.

CONSTRAINT 'email-check'
 CHECK(email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

5. Primary Key- A primary key is an attribute that uniquely identifies a tuple in a table. For Example, if we have a table with the attributes (id, first_name, last_name, salary) then in this table id attribute can be a primary key because it can identify a tuple uniquely.

-- Example
CREATE TABLE Persons (
    ID INT PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    country VARCHAR(255) DEFAULT'India',
    phoneNumber VARCHAR(10) UNIQUE,
    age INT CHECK(age >= 18)
)

6. Foreign Key- A foreign key is a special attribute that helps to make relations between two or more tables. The foreign key is that attribute that is common in all the tables included in the relationship.

CREATE TABLE Persons(
    ID INT PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    country VARCHAR(255) DEFAULT'India',
    phoneNumber CARVHAR(10) UNIQUE,
    age INT CHECK(age >= 18)
    FOREIGN KEY (orderId) REFERENCES Orders(ID)
);

The above constraint specifies that the values in the orderId column must exist in the ID column of the Orders table. In other words, any value in the orderId column must correspond to an existing row in the Orders table.

  • Alter Table Command:

The alter command is used to modify the table schema and the syntax for this command is as follows:

-- Syntax
ALTER TABLE tablename
ADD COLUMN_NAME datatype;

-- Example
ALTER TABLE user
ADD salary INT;

The ADD keyword adds a new column in the table. To delete an existing column from the database use the DROP COLUMN keyword as shown below:

-- Syntax
ALTER TABLE tablename
DROP COLUMN columnName;

-- Example
ALTER TABLE user
DROP COLUMN salary;

DQL- Data Query Language.

The DQL commands are those commands that focus on retrieving or querying data from the database. The commands in this group are for read operations.

  • Select:
    The select commands return data values in the form of a table.
--Syntax
-- SELECT * FROM TABLENAME
SELECT * FROM user;

This command has the keywords SELECT and FROM . The * means to read or display all of the attributes(columns) from the table user. If we want to display a specific attribute instead of all we can write the name of those columns separated with commas , instead of * as shown below.

SELECT Name, Age, Occupation from user;

We can apply clauses by selecting specific tuples(rows) as well. Clauses are a way to filter out the response data through given conditions. Below is a where clause which returns only those records that satisfy the condition. The below commands return the user whose id is set to 1.

SELECT * FROM user WHERE id = 1;

DML- Data ManipulationLanguage.

DML commands contain those commands that are related to the manipulation of table data such as insert update delete etc. These are as follows:

  • Insert command:

Insert command is the command that is used to add records in the table. The syntax is as follows

--Syntax
INSERT INTO tablename
VALUES (Value1, Value2, Value3)

Adding a new user to the table user:

INSERT INTO user
VALUES (1, 'John', 'Doe', 27, 'Developer')

In the above insert command syntax, we have to specify the values of all the attributes that we had to define in the schema while creating the table.

Alternatively, we can also use another syntax to provide values to specific attributes only.

--syntax
INSERT INTO tablename(colname1, colname2, colname3..)
VALUES (Value1, Value2, Value3...)
  • Update:

The update command is used to modify an existing document and the syntax for this is as follows:

-- Syntax
UPDATE TABLENAME 
SET COL1 = value, COL2 = value ... COLn = Value
WHERE colx = value;

In the below example, we have updated the age and occupation attribute of the user with id 1.

UPDATE user
SET age = 35, occupation = 'Designer'
WHERE id = 1;
  • Delete:

The delete command is used to delete existing data in the database as well as the database itself. There are different commands for deleting databases, tables, and record, these are as follows:

-- Delete a database.
DROP DATABASE databaseName;

The above command is used to delete an entire database.

-- Delete a table
DROP TABLE tableName;

The above command is used to delete an entire table along with its schema.

DELETE FROM user WHERE id = 1;

The above command is used to delete a record or tuple from the table user where id attribute is 1.

If you want to delete all the records from the table use the Truncate command, the syntax is as follows:

TRUNCATE TABLE tablename

Conclusion:

In this article, we have seen the important commands to perform basic CRUD operations. In the next article, we will see how to use these commands along with NodeJs to build a basic application.

My name is Alfiya Siddique, I am a 2nd-year diploma student studying Computer Science. You can follow me on Linkedin and Twitter.

Thank you for your time, hope you find this article helpful. Have a great day ahead!

Sql
MySQL
Database
Web Development
Dbms And Rdbms

--

--

Alfiya Siddique

Written by Alfiya Siddique

35 Followers

Student - learning Computer Science. Learning Web Development. Let's Connect- https://www.linkedin.com/in/alfiyasiddique/

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams