Technical(SQL) Flashcards
What is SQL?
Structured Query Language is a DB computer language designed to retrieve and manage data, create and modify DB schema, etc
What is Database?
Db is an organized collection of data; space for storing information
What is Query?
Request to DB to retrieve info
What is DBMS?
Database Management System is a software that controls organization, storage, retrieval, security and integrity of data in DB
What is relational DB?
DB that stores data in separate tables, rather than putting all the data in one table
What is Schema?
Logical container for DB objects that is created by user; a visual or logical representation of a relational database’s logical and visual configuration
What is Table?
Set of data elements that is organized using columns (fields) and rows (records)
What is Field?
A DB storage simplest unit
What is record?
One row of the table, represents single structured data set in a table
What is Report?
Result that you get after running query
Name popular DBs
Oracle, MS SQL Server, PostgreSQL, MySQL
What did you use SQL for?
Getting data for testing; saving data generated during testing activities; data verification in DB (find data, ensure data integrity, manipulate test data for specific tests); testing DBs
What is PK?
Primary Key is a unique identifier assigned to every record in a table
What is DB Normalization?
Process of organizing fields and tables of relationship DB to minimize redundancy and dependency including dividing large tables into smaller tables and defining relationships between them
What is a Foreign Key (FK)?
Column or combination of columns that is used to establish a relationship between tables; usually not unique and always points to a primary key of related table
How to create table in DB?
CREATE TABLE table_name(col_name1 data_type, col_name2 data_type, etc)
What does DESC do?
DESC table_name used to verify created table or find column names and datatypes
What is used to delete table?
DROP TABLE table_name
How to add, delete, modify columns in existing table?
ALTER TABLE table_name
ADD col_name datatype;
ALTER TABLE table_name
DROP COLUMN col_name;
ALTER TABLE table_name
MODIFY col_name datatype
How to add record into table?
INSERT INTO table_name VALUES ()
What is Constraint?
Used to define data integrity (restrict values in DB); e.g.: not null constraint, unique constraint, PK constraint (not null + unique), FK constraint (values in one table matches values in another), etc
What is UPDATE used for?
To change data in a table. UPDATE table_name SET col1 = val1, [col2 = val2, etc] [WHERE conditions]
How to delete records from table?
DELETE FROM table WHERE conditions
