SQL Structured Query Language Portnov

SQL Structured Query Language Portnov

memorize.aimemorize.ai (lvl 286)
Section 1

Preview this deck

Foreign Key

Front

Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

2 years ago

Date created

Mar 14, 2020

Cards (23)

Section 1

(23 cards)

Foreign Key

Front

Foreign key is a table column (or columns) that links the table row(s) to another table's primary key. Foreign key enforces relationship between two tables via primary key of the parent table and foreign key of the dependent child table.

Back

ALTER - DDL Statement

Front

Is used to modify a DB object and data ALTER table test_user ADD dob date;

Back

Database Tables

Front

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

Back

DCL - Data Control Language

Front

Is used for data management: COMMIT ROLLBACK GRANT REVOKE

Back

SQL Statements

Front

SQL Statements Most of the actions you need to perform on a database are done with SQL statements. The following SQL statement selects all the records in the "Customers" table: SELECT * FROM Customers;

Back

SQL Structured Query Language

Front

is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).

Back

The SQL SELECT Statement

Front

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set.

Back

SQL language is subdivided into several language elements, including:

Front

Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)[17] Expressions, which can produce either scalar values, or tables consisting of columns and rows of data Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values and are used to limit the effects of statements and queries, or to change program flow. Queries, which retrieve the data based on specific criteria. This is an important element of SQL. Statements, which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics. SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.

Back

Data definitions

Front

(column names, data format, etc) are stored within database dictionaries . Table ROW may be called RECORD Data records are stored in the database files on the disks.

Back

Schema

Front

Schema - logical container for database objects (tables, views, index, triggers). It is identified by user ID and Password.

Back

INSERT - DML Statement

Front

INSERT statement allows to insert record into a table. Syntax: String values shall be encapsulated in single quotes. If Autocommit is checked, record will be permanently added.

Back

Normalization

Front

Solution is normalization by creating two related tables, each serving specific purpose and balance the data and relations across the tables.

Back

Table

Front

Table - container for data elements and relations Table COLUMN may be called FIELD or ATTRIBUTE

Back

The scope of SQL includes

Front

data insert, query, update and delete, schema creation and modification, and data access control.

Back

Where a tester can use SQL

Front

Creating data for testing Saving data generated during testing activity Data verifications in a database to find data to ensure data integrity (quality) to manipulate test data for specific tests Gray box testing

Back

PRIMARY KEY

Front

A table column (or a combination of columns), which uniquely identifies each record of the table is a primary key. Examples of Primary Keys: Social Security Number (SSN) Drivers License ID

Back

Some of The Most Important SQL Commands

Front

SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index

Back

DML - Data Manipulation Language

Front

Is used for data management: INSERT UPDATE SELECT DELETE

Back

DDL - Data Definition Language

Front

Is used to create, modify, and remove the DB object definition and data: CREATE DROP ALTER TRUNCATE

Back

Databases terminology

Front

Query - a request to retrieve the data from one or multiple DB tables. Result Set - a number of retrieved records

Back

SQL consists of

Front

data definition language, data manipulation language, and a data control language.

Back

SQL SELECT Syntax

Front

SELECT column_name,column_name FROM table_name; SELECT * FROM table_name;

Back

What is SQL?

Front

SQL is a database computer language designed to: Create, modify, and relate the database objects within relational databases Select and manipulate data

Back