Basic of SQL Queries

What is Database:

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. The data can then be easily accessed, managed, modified, updated, controlled, and organized.

What is SQL:

SQL (Structured Query Language) is a language for managing data in a database. SQL is referred as “special purpose” or “domain specific” programming language because you can not use it to build web app, but you use it to interact with database that powers the web app.

Practice basic queries in SQL

let say we want to create Students table that has four columns first_name, last_name, grade, GPA.

CREATE TABLE students (
id INTEGER PRIMARY KEY,
firstName TEXT,
lastName TEXT,
grade INTEGER,
gpa FLOAT);

SQL is not case sensitive but by convention we capitalize SQL commands.

To view the table you create:

.schema

to drop table:

DROP TABLE students;

to insert data to your database:

INSERT INTO students (firstName, lastName, grade, gpa) VALUES ('Amanda', 'Smith', 9, 3.51 );

A basic SELECT statement works like this:

SELECT * FROM students; //to select all columns
SELECT firstName FROM students; //select only firstName column

If you have duplicate data (for example, two students with the same name) and you only want to select unique values, you can use the DISTINCTkeyword. For example:

SELECT DISTINCT firstName FROM students;

Selecting Based on Conditions: The WHERE Clause

SELECT * FROM students WHERE FirstName = "Amanda";

to update data in our table rows. We do this with the UPDATE keyword.

UPDATE students SET firstName = "Hana" WHERE name = "Amanda";

to delete table rows we use UPDATE statement :

DELETE FROM students WHERE id = 1;

ORDER BY modifier allows us to order the table rows returned by a certain SELECT statement.

SELECT * FROM students ORDER BY firstName;

will return names order alphabetically. By default ORDER BY command order in ascending order ASC to order in descending order use DESC

SELECT * FROM students ORDER BY firstName DESC;

LIMIT is used to determine the number of records you want to return from a dataset. For example:

SELECT * FROM students ORDER BY gpa DESC LIMIT 1;

to return students with GPA between 2.5 to 3.5 use BETWEEN

SELECT gpa FROM students WHERE gpa BETWEEN 2.5 AND 3.5;

to count how many students with GPA 3.5

SELECT COUNT(gpa) FROM students WHERE gpa = 3.5;

That’s all!

I hope you guys appreciate the content and learn more about SQL Databases.

more practice on SQL

SQL Aggregate Functions

SQL Tutorial