LPIC1 105.3 SQL Data Management

Part 1. Theory and Command Overview

SQL (Structured Query Language) is a universal language for managing relational databases. It allows you to create, modify, populate, and query tables. A DBMS (Database Management System) is the software that stores and provides access to data (e.g., MySQL, MariaDB, PostgreSQL, SQLite).

Basic Database Concepts

• Database — a set of related tables.
• Table — consists of rows (records) and columns (fields).
• Record (row) — a single row representing one object.
• Field (column) — a column representing an attribute of the object.

DBMS and Starting MySQL

• Install MySQL on Ubuntu/Debian:
  sudo apt-get install mysql-server
• Start MySQL:
  mysql -u root -p

Basic SQL Commands

• CREATE DATABASE db_name; — create a new database.
• USE db_name; — select a database to work with.
• CREATE TABLE ... — create a table with specified fields and types.
• SHOW TABLES; — list all tables.
• DESCRIBE table_name; — show the structure of a table.
• INSERT INTO ... VALUES (...); — insert a record.
• SELECT ... FROM ...; — query data.
• UPDATE ... SET ... WHERE ...; — modify data by condition.
• DELETE FROM ... WHERE ...; — delete records by condition.
• LOAD DATA LOCAL INFILE ... INTO TABLE ...; — import data from a file.
• JOIN, ORDER BY, GROUP BY — join, sort, and group data.

Part 2. Practice: Basic SQL Queries and Operations

Example: Creating a Database and Tables

mysql -u root -p
CREATE DATABASE cars;
USE cars;
CREATE TABLE new (
  id INT AUTO_INCREMENT PRIMARY KEY,
  brand VARCHAR(10),
  model VARCHAR(10),
  year YEAR,
  price INT
);
CREATE TABLE used (
  id INT AUTO_INCREMENT PRIMARY KEY,
  brand VARCHAR(10),
  model VARCHAR(10),
  year YEAR,
  price INT
);
SHOW TABLES;
DESCRIBE new;
EXIT;
  

Importing Data from a Text File

• Create new.txt with tab-separated values:
  KIA CERATO 2014 780000
• Import data into the table:
  mysql --local-infile=1 -u username -p
USE cars;
LOAD DATA LOCAL INFILE "new.txt" INTO TABLE new;
• Check records:
  SELECT * FROM new;

Inserting, Deleting, and Updating Data

INSERT INTO new (brand, model, year) VALUES ('Daewoo', 'Nexia', 2015);
DELETE FROM new WHERE model = 'Matiz';
UPDATE new SET model = 'KUGA' WHERE model = 'FOCUS';
  

Basic SELECT Queries

• SELECT * FROM new; — select all records from the table.
• SELECT model FROM new; — select only the model column.
• SELECT * FROM new WHERE brand = "KIA"; — select all records where brand is KIA.
• SELECT * FROM new WHERE brand = "KIA" AND year = 2015; — with multiple conditions.
• SELECT * FROM new JOIN used ON new.brand = used.brand; — join records where brand matches (JOIN).
• SELECT * FROM new ORDER BY price; — sort by price.
• SELECT * FROM new GROUP BY brand; — group by brand.

What You Need to Know for the LPIC Exam

• Understand relational database concepts: tables, fields, records.
• Be able to create databases and tables, define field types, and set PRIMARY KEY.
• Perform basic operations: SELECT, INSERT, UPDATE, DELETE, JOIN, ORDER BY, GROUP BY.
• Import data from files.
• Understand SQL query structure, filtering, and sorting syntax.
• Work safely with databases: end sessions, use only necessary permissions.