SQL And Databases

A database is an organized collection of structured data stored and accessed electronically from a computer system. Databases allow efficient storage, retrieval, and management of data using software called a Database Management System (DBMS). Common examples include relational databases like MySQL and non-relational ones like MongoDB.

An RDBMS is a software system that manages relational databases, organizing data into tables with rows and columns related by keys. It supports SQL for querying and ensures data integrity through constraints. Examples include PostgreSQL, Oracle, and SQL Server.

Databases are broadly classified into relational (SQL-based, like MySQL) and non-relational (NoSQL, like MongoDB for documents or Cassandra for wide-column stores). Other types include hierarchical, network, and graph databases for specific use cases.

SQL stands for Structured Query Language, a standard language for managing and manipulating relational databases. It includes sublanguages like DDL for schema definition, DML for data manipulation, and DCL for access control.

Popular SQL dialects include MySQL, PostgreSQL, SQLite, Oracle SQL, and Microsoft SQL Server, each with slight syntax variations but adhering to ANSI SQL standards. PostgreSQL is closest to the standard, while MySQL has extensions like GROUP BY shortcuts.

A table is a structured data storage unit in a relational database, consisting of rows (records) and columns (attributes). Each column has a defined data type, and tables relate via keys.

A primary key is a unique identifier for each record in a table, ensuring no duplicates and enabling efficient lookups. It can be a single column or composite, like PRIMARY KEY (id).

A foreign key is a column or set of columns in one table that references the primary key of another table, enforcing referential integrity. For example, an orders.customer_id foreign key links to customers.id.

DDL stands for Data Definition Language, used to define and manage database structures like tables, indexes, and views. Common commands: CREATE, ALTER, DROP.

The CREATE TABLE statement defines a new table with specified columns, data types, and constraints. Example: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));.

The ALTER TABLE command modifies an existing table's structure, such as adding/dropping columns or constraints. Example: ALTER TABLE users ADD email VARCHAR(100);.

DROP TABLE permanently deletes a table and all its data from the database. Use with caution: DROP TABLE temp_table;.