Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

EXAM SQL

memorize.ai (lvl 286)

(0)

36 cards

()

Section 1

Preview this deck

Where Operators: Equal to Not equal to greater than less than greater or equal to less than or equal to is null (empty) is not null (not empty) greater than a and less than b (inclusive) search for a pattern

Front

1 / 36

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (36)

Section 1

(36 cards)

Where Operators: Equal to Not equal to greater than less than greater or equal to less than or equal to is null (empty) is not null (not empty) greater than a and less than b (inclusive) search for a pattern

Front

= <> > < >= <= IS NULL IS NOT NULL BETWEEN a AND b LIKE

Back

code a LEFT JOIN:

Front

LEFT JOIN ON

Back

What does My SQL stand for?

Front

structured query language

Back

What clause is used to combine records from multiple tables based on table relationships?

Front

LEFT JOIN clause

Back

database management system (DBMS)

Front

software for creating and managing databases. Provides interaction between users, applications, and databases

Back

What are VIEWS? and what do they contain?

Front

virtual tables created from SQL queries rows/columns **they are just stored SQL statements. changes to tables are automatically reflected in views.

Back

normalization:

Front

process of organizing data to avoid data redundancy....makes databases more efficient

Back

How do you create a VIEW? code it

Front

CREATE VIEW view name AS (SQL STATEMENT) SELECT columns FROM table LEFT JOIN table ON table.column = table.column WHERE table.column = value;

Back

How do you Edit a VIEW?

Front

CREATE OR REPLACE VIEW view name AS SELECT columns FROM table LEFT JOIN table ON table.column = table.column WHERE table.column = value;

Back

Aggregate Functions: count number of rows find column's min/max value calculate column's average value sum column's total values generate a random number between 0 and 1 (what is it used with?) return length of given string concatenate strings

Front

SELECT COUNT() FROM table WHERE column = value; SELECT MIN (milliseconds), MAX(milliseconds) FROM table SELECT AVG(milliseconds) FROM table WHERE column = value; SELECT SUM(milliseconds) FROM table WHERE column = value SELECT FROM table ORDER BY RAND(); [used with ORDER BY clause to SELECT records in random order] SELECT column (if needed), CHAR_LENGTH(column) AS name FROM table; SELECT CONCAT(column, 'text here', column) FROM table;

Back

Why use VIEWS?

Front

Security: only make certain rows and columns available Simplicity: No need to keep joining tables for every query

Back

What does the FROM command do? How do you separate?

Front

specifies tables to retrieve from DB. with commas

Back

How do you delete existing records? define What is a good tip to remember? What happens if the WHERE clause is not included?

Front

DELETE FROM table WHERE column(primary key) = value Perform SELECT statement before deleting to verify all records will be deleted

Back

what does the ORDER BY command do? Name both commands and which is the default?

Front

sorts data by specified columns. ASC (default) DESC

Back

Primary key

Front

a field or set of fields that uniquely identify each record in a table

Back

How do you delete a VIEW?

Front

DROP VIEW view name;

Back

What is a database?

Front

collection of information organized so that it can be easily accessed and managed

Back

foreign key

Front

a field or set of fields that uniquely identify a record in a another table

Back

How do you add new records? Define each clause

Front

INSERT INTO table (column, column, etc.) VALUES (same order as above) INSERT INTO specifies table and columns to add data to VALUES specifies values to be added in each column...1 to 1 correlation

Back

relational database management system (RDBMS)

Front

DBMS for relational databases

Back

when looking at a table of data...what is the column and what is the field?

Front

|c| blue, box/red

Back

What does a SELECT command do? how do you separate multiple? How do you signify all columns?

Front

specifies columns to retrieve from database. with commas * asterisk

Back

How do you temporarily rename a column?

Front

Aliasing AS

Back

relational databases:

Front

logical connection between different tables

Back

show LIKE operator code example:

Front

WHERE name LIKE ______

Back

are SQL commands case-sensitive?

Front

No, but capitalizing SQL statements is good programming practice.

Back

How do you update existing records? define each clause

Front

UPDATE table SET column = value, column = value, etc.... WHERE column = value (always use primary key for where clause here)

Back

code a GROUP BY clause:

Front

SELECT columns, COUNT(*) FROM table JOIN table ON table.column = table.column GROUP BY table.column;

Back

what is the LIKE operator usually used with?

Front

wildcards %

Back

How do you resolve column name conflicts?

Front

prefix it with the table name.....table.column

Back

what type of quotes do we use for strings in sql statements?

Front

single 'blah'

Back

What does the WHERE command do?

Front

specifies conditions while retrieving data

Back

Kind of data that each field can store: -numeric -strings -data & time -boolean

Front

1. int, float, decimal 2. char, varchar 3. date, datetime, time, year 4. bit

Back

what does the GROUP BY clause do?

Front

groups results by data in specified columns...often used with aggregate functions

Back

How do you join multiple conditions or WHERE clauses? and how do you code it to ensure order of operations and code readability?

Front

AND / OR use parenthesis

Back

stages to serving dynamic pages:

Front

1. web server processes server-side code using interpreters. 2. interpreter executes code and manipulates database if necessary. 3. html page is generated and then returned to client

Back