Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

Common SQL commands

Anusheh Zohair Mustafeez

SQL is a vital part of database management systems. It provides a lot of useful functionality to store and later update data. In this shot, we will cover the most commonly used SQL commands, namely:

  • CREATE DATABASE: Creates a new database
  • CREATE TABLE: Creates a table in a database
  • INSERT INTO: Inserts rows into a table
  • SELECT: Selects and displays data from a table
  • UPDATE: Updates values in a table
  • DELETE: Deletes rows in a table
  • DROP TABLE: Deletes tables in a database

How to create database

Before we can manipulate data, we need to have a database to store it in. This can be done using CREATE DATABASE followed by the name of the database we need to make. For our purpose, let’s create a database called edpresso that’llstore tables of edpresso shots and the names of edpresso employees. If you are using MySQL, type in the following command to create the database:

CREATE DATABASE edpresso;

For sqlite3, type:

sqlite3 edpresso.db

The screenshots in this shot will be from commands run on sqlite3.

How to create a table

Now that we have our database, let’s create an employee table with shot table in it. For the first table, type the following:

CREATE TABLE Employees (
    ID int,
    Name varchar(255),
    Age int,
    Position varchar(255)
);
CREATE TABLE Shots (
    ID int,
    Title varchar(255),
    Category varchar(255)
);

Now, to check if your tables have been made, type .tables to see all the tables in your database.

widget

How to insert data

Now that our tables are ready, we can insert data into them. To add a new row of data to our tables, use the following syntax:

INSERT INTO Employees
VALUES (ID_Value, Name_Value, Age_Value, Position_Value);
INSERT INTO Shots
VALUES (ID_Value, Title_Value, Category_Value);

Using INSERT INTO, we can add multiple rows into our tables. In the picture below, four rows were available to the Employees table, and two rows were added to the Shots table.

widget

How to view data

Now that we have data in our table, we can use the SELECT command to view them. If we wish to view all the data in a table, we can use the * wildcard and list the table name after the FROM clause, like this:

SELECT * FROM tablename;

Let’s do this for our tables and see what shows up:

widget

Now, what if we want to see only certain columns from our Employees table? SQL allows us to do this by specifying the column names and separating them by commas. This is shown below:

SELECT columnname1, columnname2, ... FROM tablename;

Let’s see if we can fetch employee names and their positions from the Employees table with the above syntax:

widget

We also filter rows based on values using the WHERE clause. The syntax for this is:

SELECT columnname1, columnname2, ...
FROM tablename
WHERE condition

Let’s retrieve the names of all employees who are older than 30 using the WHERE clause:

widget

How to update data

Now, let’s suppose we realized we have the name of the employee with ID 4 spelled wrong, and we need to update his name from ‘Muaaz’ to ‘Mueez’. The UPDATE command would be the simplest way of changing this. The generic syntax for it is:

UPDATE tablename
SET columnname1 = value1, columnname2 = value2, ...
WHERE condition;

Let’s do this for our specific context and then display the results with a SELECT statement:

widget

How to delete data

There are times when rows in a table need to be deleted. For instance, let’s suppose Maria is moving and needs to resign. To delete her data from the Employees table, we would use the DELETE command, like this:

DELETE FROM tablename WHERE condition;

Let’s try this for our case:

widget

How delete a table

Finally, let’s go over how we can delete a table from a database. The syntax for this is:

DROP TABLE tablename;

Let’s use this command to drop the Shots table:

widget

Putting it all together

In the widget below, we’ve put all the commands studied above together. Play around with the code and see what changes you can make to the tables:

-- Creating a Database
CREATE DATABASE edpresso;

-- Creating the Employees table
CREATE TABLE Employees (
    ID int,
    Name varchar(255),
    Age int,
    Position varchar(255)
);

-- Creating the Shots table
CREATE TABLE Shots (
    ID int,
    Title varchar(255),
    Category varchar(255)
);

-- Inserting entries into the Employees table
INSERT INTO Employees
VALUES (1, 'Maria', 34, 'Marketing Head');
INSERT INTO Employees
VALUES (2, 'Saad', 26, 'Developer Advocate');
INSERT INTO Employees
VALUES (3, 'Farah', 29, 'Software Engineer');
INSERT INTO Employees
VALUES (4, 'Muaaz', 45, 'HR Officer');

-- Inserting entries into the Shots table
INSERT INTO Shots
VALUES (1, 'What is the dining philosophers problem?', 'Operating Systems');
INSERT INTO Shots
VALUES (2, 'What is a database schema?', 'Databases');

-- Displaying all entries in the Employees table
SELECT * FROM Employees;

-- Displaying all entries in the Shots table
SELECT * FROM Shots;

-- Displaying filtered entries in the Employees table
SELECT Name, Position
FROM Employees
WHERE Age > 30;

-- Updating Name Muaaz to Mueez in the Employees table
UPDATE Employees
SET Name = 'Mueez'
WHERE ID = 4;

-- Displaying updated Employees tables
SELECT * FROM Employees;

-- Deleting Maria's entry from the Employees table
DELETE FROM Employees WHERE Name = 'Maria';

-- Displaying updated Employees tables
SELECT * FROM Employees;

-- Dropping the Shots table
DROP TABLE Shots;

RELATED TAGS

sql

CONTRIBUTOR

Anusheh Zohair Mustafeez
RELATED COURSES

View all Courses

Keep Exploring