SQL

SQL is a domain-specific language designed to manage and query data held in a relational database management system (RDBMS).

Basic SQL Commands:

• SELECT: Used to retrieve data from a database table.

  SELECT column1, column2
  FROM table_name
  WHERE condition;

• Retrieve all columns:

  SELECT * FROM table_name;

• INSERT INTO: Used to insert new data into a table.

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

• UPDATE: Used to modify existing data in the table.

  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

• DELETE: Used to delete records from a table.

  DELETE FROM table_name WHERE condition;

• CREATE TABLE: Used to create a new table.

  CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
  );

• ALTER TABLE: Used to modify an existing table (e.g., add, delete, modify columns).

  ALTER TABLE table_name
  ADD column_name datatype;

• DROP TABLE: Used to delete an existing table and all its data.

  DROP TABLE table_name;

WHERE Clause:

The WHERE clause is used to filter records. It is used to extract only the records that fulfill a specified condition.

SELECT * FROM table_name WHERE column_name operator value;

• = Equal
• <> Not equal (in some databases != can also be used)
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal
• BETWEEN Between a range
• LIKE Search for a pattern

SQL Joins:

SQL Joins are used to combine rows from two or more tables based on a related column between them.

ORDER BY:

The ORDER BY keyword is used to sort the result set by one or more columns.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

SQL Constraints:

Constraints are used to specify rules for the data in a table:

• PRIMARY KEY: Uniquely identifies each record in a table.
• FOREIGN KEY: Ensures referential integrity of data in one table to match values in another table.
• UNIQUE: Ensures that all values in a column are different.
• NOT NULL: Ensures that a column cannot have a NULL value.

Exercises

Login

Follow the steps

show databases;
drop database logindb;
create database logindb;
use logindb;

create table login (
name varchar(255),
password varchar(255)
);
describe login;

insert into login values (‘piet’, ‘geheim’);
insert into login values (‘john’, ‘secret’);

select * from login;
select naam from login;
select * from login where name = ‘piet’;

delete from login where name = ‘john’

Todo

• Create a tododb database.
• Create a todo table with id and task fields
• Add todo records (insert)
• Show the records (select) Delete the last record