Databases and SQL Overview

Questions and Answers

What is a Database?

A collection of interrelated data.

What does DBMS stand for?

Database Management System

What is RDBMS?

Relational Database Management System

What does SQL stand for?

Structured Query Language Signup and view all the answers

What operations can you perform using SQL?

CREATE Signup and view all the answers

SQL keywords are case sensitive.

False Signup and view all the answers

What is the difference between SQL and MySQL?

SQL is a language, while MySQL is an RDBMS that uses SQL. Signup and view all the answers

What is CHAR in SQL?

A data type that can store characters of fixed length. Signup and view all the answers

What is VARCHAR in SQL?

A data type that can store characters up to a given length. Signup and view all the answers

What is BLOB in SQL?

A data type that can store binary large objects. Signup and view all the answers

Match the SQL data types with their descriptions:

INT = Integer values within a specific range FLOAT = Decimal number with precision to 23 digits BOOLEAN = Boolean values 0 or 1 DATE = Date in format of YYYY-MM-DD Signup and view all the answers

Which of the following are types of SQL commands?

DDL Signup and view all the answers

What is DDL in SQL?

Data Definition Language Signup and view all the answers

What is a key command of DDL?

CREATE TABLE Signup and view all the answers

Study Notes

Databases and DBMS

A database is a collection of interrelated data.
Database Management System (DBMS) is software to create, manage, and organize databases.
Relational Database Management System (RDBMS) is a type of DBMS that organizes data into tables (relations), consisting of rows (records) and columns (attributes).
Examples of RDBMS include MySQL, PostgreSQL, and Oracle.

Structured Query Language (SQL)

SQL, or Structured Query Language, is used to store, manipulate, and retrieve data from RDBMS.
SQL is essential for performing CRUD operations:
- CREATE: Creating databases, tables, and inserting data.
- READ: Retrieving data from the database.
- UPDATE: Modifying existing data.
- DELETE: Removing data or whole tables from the database.
SQL keywords are not case sensitive (e.g., SELECT and select are equivalent).

SQL vs MySQL

SQL is the language for interacting with relational databases, while MySQL is an RDBMS that implements SQL.

SQL Data Types

Data types define what kind of data can be stored in a column or variable.
Commonly used data types in MySQL include:
- CHAR: Fixed-length string (0-255).
- VARCHAR: Variable-length string (0-255).
- BLOB: Binary large object storage (0-65535).
- INT: Integer (-2,147,483,648 to 2,147,483,647).
- FLOAT: Decimal number with up to 23 digits of precision.
- BOOLEAN: Represents true or false values (0 or 1).
- DATE: Date format YYYY-MM-DD.
Consider using UNSIGNED with integer types for non-negative values.

Types of SQL Commands

DQL (Data Query Language): Retrieves data from databases (e.g., SELECT).
DDL (Data Definition Language): Manages database structure (e.g., CREATE, DROP, ALTER).
DML (Data Manipulation Language): Modifies data (e.g., INSERT, UPDATE, DELETE).
DCL (Data Control Language): Grants or revokes permissions (e.g., GRANT, REVOKE).
TCL (Transaction Control Language): Manages database transactions (e.g., COMMIT, ROLLBACK).

Data Definition Language (DDL)

DDL commands manage the structure of databases and their objects.
Key DDL Commands:
- CREATE TABLE: Creates a new table specifying name, columns, data types, and constraints (e.g., CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary DECIMAL(10, 2));).
- ALTER TABLE: Modifies an existing table's structure (e.g., ALTER TABLE employees ADD COLUMN email VARCHAR(100);).
- DROP TABLE: Deletes an existing table along with its data (e.g., DROP TABLE employees;).
- CREATE INDEX: Enhances query performance by creating an index (e.g., CREATE INDEX idx_employee_name ON employees (name);).

Description

Explore the concepts of databases and Database Management Systems (DBMS) with a focus on Structured Query Language (SQL). This quiz will cover the key principles of relational databases, critical SQL operations, and compare SQL with MySQL. Test your knowledge on these foundational topics in data management.