SQL Database Flashcards
What is Database ?
Organized collection of data
Storage space for content/information (data)
Advantages:
• Control of data accuracy
• Data consistency
• Data manipulation (sort, retrieve, update…)
• Data Security
Disadvantages:
• Complexity, size, cost, maintenance
What is a Relational database? Advantages?
Relational databases use TABLES to store information.
It allows you to easily find specific information, sort based on any field, and generate reports that contain only certain fields from each record.
What is Schema?
Logical container for database objects (tables, views, triggers) that user creates
What is a Table? Can we have an empty Table?
Table is set of data elements (values) that is organized using columns (fields) and rows (records)
We can have an empty table
What is Field? What ‘part’ of the table?
Field is a database storage simplest unit (table column)
What is Record? What ‘part’ of the table?
Record is a row and it represents a single structured data set in table
What is Query?
Query is your request to the database to retrieve information
What is Report? How much information can you get in one report?
Report is the returned information to the specified query
Reports can be tailored to the needs of the data-user, making the information they extract much more useful
What is DBMS? Name a few popular databases
Database Management System is a software that controls the organization, storage, retrieval, security and integrity of data in dB
Popular Databases: ORACLE, Sybase, MySQL , DB2.
What is a PRIMARY KEY (PK) / FOREIGN KEY (FK)?
PRIMARY KEY (PK) is a unique identifier of every record in a table;
FOREIGN KEY (FK) is a column (or combination of columns) that is used to establish a relationship between the tables;
Foreign key is usually not unique (one-to-many relation) and shall always point to a primary key
What is a database NORMALIZATION?
NORMALIZATION is the dB process of dividing large tables into smaller tables and defining relationships between them;
What is SQL stands for? What does it do?
SQL stands for Structured Query Language. It is a database computer language, designed to retrieve and manage data, create and modify dB schema
Name a few Schema commands; Describe
CREATE - creates a table (index, view) in a database.
CREATE TABLE table1 (ID int, Last_Name varchar(255))
ALTER - used to add, delete, or modify columns in an existing table.
ALTER TABLE table1 ADD column1 varchar(255)
ALTER TABLE table1 DROP COLUMN column1
ALTER TABLE table1 MODIFY column1 varchar (255)
DROP - deletes a table (index, view).
DROP TABLE table1
TRUNCATE - deletes data in the table, but not table itself
TRUNCATE TABLE table1
COMMENT
RENAME
How to add a record to the table?
INSERT INTO table1 VALUES
How to change one field in the table?
ALTER TABLE table1 (MODIFY or RENAME COLUMN)
UPDATE table1 SET value1 WHERE field1=‘value2’
What is the difference between DROP, DELETE and TRUNCATE?
TRUNCATE removes ALL records from the table, same as DELETE FROM without WHERE clause
DROP deletes whole table
What is Constraint ? Give couple constraints examples?
Constraint is used to define data integrity, restrict the values in a database
NOT NULL, PK, FK
How to read data from dB?
SELECT * FROM table1
What are the minimum requirements for the SELECT statement?
Two: SELECT, FROM
Is WHERE required in the SELECT statement? Why?
Only SELECT and FROM clause are required. WHERE clause is used to specify the certain condition, not select the whole table
How can I retrieve information for the whole table?
SELECT * FROM table1
How would you read all first names from CUSTOMER table?
SELECT first_name FROM customer
Is it possible to have duplicate first names in CUSTOMER table? How would you read only unique names?
SELECT DISTINCT first_name FROM customer
How would you retrieve all employees 55+ years old, living in cities, starting with ‘San’, showing the oldest employee first?
SELECT * FROM employees
WHERE age>55 AND city LIKE ‘San%’
ORDER BY age DISC
