Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Tin hoc co so > Pro lang > Flashcards

Pro lang Flashcards

1
Q

SQL

A
  • Structured Query Language

- SQL lets you access and manipulate databases

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Basic Queries(in SQL)

A
  • Querying data: SELECT
  • Sorting data: ORDER BY
  • Filtering data: WHERE, AND, OR, IN, BETWEEN, LIKE, IS NULL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SQL SELECT

A
  • SELECT statement to retrieve data from all columns example

SELECT lastname, firstname, jobtitle
FROM employees;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

ORDER BY(SQL)

A

SELECT select_list
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Sort values in multiple columns (SQL)

A

SELECT contactLastname, contactFirstname
FROM customers
ORDER BY contactLastname, contactFirstname;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Sort a result set by an expression example

A

SELECT orderNumber, orderLineNumber, quantityOrdered * priceEach AS subtotal
FROM orderdetails
ORDER BY subtotal DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

WHERE clause(SQL)

A 
-
SELECT select_list 
FROM table_name 
WHERE search_condition;
- VD:
SELECT lastname, firstname, jobtitle FROM employees 
WHERE jobtitle = 'Sales Rep';
  • The search_condition is a combination of one or more predicates using the logical operator AND, OR and NOT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

WHERE clause with AND operator(SQL)

A

SELECT lastname, firstname, jobtitle, officeCode
FROM employees
WHERE jobtitle = ‘Sales Rep’ AND officeCode = 1;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

WHERE clause with comparison operators(SQL)

A

= : equal to

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

AND, OR and NOT Operators(SQL)

A
  • The WHERE clause can be combined with AND, OR, and NOT operators.
  • The AND and OR operators are used to filter records based on more than one condition:
    + The AND operator displays a record if all the conditions separated by AND are TRUE.
    + The OR operator displays a record if any of the conditions separated by OR is TRUE.
  • The NOT operator displays a record if the condition(s) is NOT TRUE.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

AND Syntax(SQL)

A

SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
Vd:
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

OR Syntax(SQL)

A 
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Vd:
SELECT * FROM Customers
WHERE City='Berlin' OR City='München';
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

NOT Syntax

A 
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Vd:
SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain';
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Combining AND, OR and NOT

A

Vd1:
SELECT * FROM Customers
WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);

Vd2:
SELECT * FROM Customers
WHERE NOT Country=’Germany’ AND NOT Country=’USA’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

INSERT INTO Statement(SQL)

A

is used to insert new records in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

INSERT INTO Syntax

A

S1:
Specify both the column names and the values to be inserted:
> INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
S2:
> If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
> INSERT INTO table_name
VALUES (value1, value2, value3, …);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

SELECT DISTINCT statement

A

is used to return only distinct (different) values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

SELECT DISTINCT Syntax

A

SELECT DISTINCT column1, column2, …

FROM table_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

SELECT COUNT(DISTINCT Country) FROM Customers;

A

lists the number of different (distinct) customer countries:

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

a NULL Value

A

is a field with no value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How to Test for NULL Values?

A

It is not possible to test for NULL values with comparison operators, such as =, .

We will have to use the IS NULL and IS NOT NULL operators instead

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

(NOT) IS NULL Syntax

A

SELECT column_names
FROM table_name
WHERE column_name (NOT) IS NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

UPDATE Statement

A

is used to modify the existing records in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

UPDATE Syntax

A

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

DELETE Syntax

A
  • is used to delete existing records in a table

> DELETE FROM table_name WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

SELECT TOP Clause

A
  • is used to specify the number of records to return.
  • The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
  • **Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Other types of SELECT TOP

A

1/QL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
MySQL Syntax:

2/SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Oracle 12 Syntax:
3/SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
Older Oracle Syntax:

4/SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Older Oracle Syntax (with ORDER BY):

5/SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
WHERE ROWNUM <= number;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

MIN() Syntax

MAC() Syntax

A

SELECT MIN(column_name)
FROM table_name
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

AVG() Syntax

A
  • returns the average value of a numeric column.:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

COUNT() Syntax

A
  • returns the number of rows that matches a specified criterion:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

SUM() Syntax

A
  • returns the total sum of a numeric column:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

LIKE Operator

A
  • is used in a WHERE clause to search for a specified pattern in a column.
  • There are two wildcards often used in conjunction with the LIKE operator:
    + The percent sign (%) represents zero, one, or multiple characters
    + The underscore sign () represents one, single character
    **Note: MS Access uses an asterisk () instead of the percent sign (%), and a question mark (?) instead of the underscore (    ).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

LIKE Syntax

A

SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;

34
Q

…

A

LIKE Operator Description(Find any values)
WHERE CustomerName LIKE ‘a%’= start with “a”
WHERE CustomerName LIKE ‘%a’= end with “a”
WHERE CustomerName LIKE ‘%or%’= have “or” in any position
WHERE CustomerName LIKE ‘r%’=have “r” in the second position
WHERE CustomerName LIKE ‘a%’= start with “a” and are at least 2 characters in length
WHERE CustomerName LIKE ‘a__%’=start with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ = start with “a” and ends with “o”

35
Q

IN Operator

A
  • The IN operator allows you to specify multiple values in a WHERE clause.
  • The IN operator is a shorthand for multiple OR conditions.
36
Q

IN Syntax

A

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

37
Q

BETWEEN Operator

A
  • The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
  • The BETWEEN operator is inclusive: begin and end values are included.
38
Q

BETWEEN Syntax

A

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

39
Q

Aliases

A

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

40
Q

Alias Column Syntax

A

SELECT column_name AS alias_name

FROM table_name;

41
Q

Alias Table Syntax

A 
SELECT column_name(s)
FROM table_name AS alias_name;
42
Q

Different Types of SQL JOINs

A
  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
43
Q

SQL JOIN

A

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

44
Q
  • INNER JOIN Syntax
  • LEFT JOIN Syntax
  • RIGHT JOIN Syntax
  • FULL (OUTER) JOIN Syntax
A

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

45
Q

SQL UNION Operator

A
  • The UNION operator is used to combine the result-set of two or more SELECT statements.+ Every SELECT statement within UNION must have the same number of columns
    + The columns must also have similar data types
    + The columns in every SELECT statement must also be in the same order
46
Q

UNION Syntax

UNION ALL Syntax

A 
SELECT column_name(s) FROM table1
UNION (ALL)
SELECT column_name(s) FROM table2;
47
Q

GROUP BY statement

A

groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

48
Q

GROUP BY Syntax

A 
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
49
Q

HAVING Clause

A

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

50
Q

HAVING Syntax

A 
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
51
Q

EXISTS Operator

A

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

52
Q

EXISTS Syntax

A

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

53
Q

SQL ANY and ALL Operators

A
  • The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
  • The ANY operator:
    +returns a boolean value as a result
    + returns TRUE if ANY of the subquery values meet the condition
  • ANY means that the condition will be true if the operation is true for any of the values in the range
54
Q

ANY Syntax

A 
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);
55
Q

ALL Syntax With SELECT

A

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

56
Q

ALL Syntax With WHERE or HAVING

A 
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);
57
Q

SELECT INTO statement

A

copies data from one table into a new table.

58
Q

SELECT INTO Syntax
+ coppy all columns into new table
+ coppy some columns

A

SELECT */ SELECT column 1, column 2….
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

59
Q

INSERT INTO SELECT statement

A
  • copies data from one table and inserts it into another table.
  • requires that the data types in source and target tables matches.
60
Q

INSERT INTO SELECT Syntax

A

Copy all/some columns from one table to another table:

INSERT INTO table2/table 2( column 1, column 2….)
SELECT * FROM table1
WHERE condition;

61
Q

SQL CASE Statement

A

The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

62
Q

CASE Syntax

A 
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
63
Q

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

A
  • Vd IFNULL:
    SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
    FROM Products;
64
Q

a Stored Procedure

A
  • is a prepared SQL code that you can save, so the code can be reused over and over again.
  • So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
  • You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
65
Q
  • Stored Procedure Syntax
  • Execute a Stored Procedure
A 
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
----------------------------------------------------------
EXEC procedure_name;
66
Q

SQL Comments

A

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

67
Q

Single Line Comments

A

Single line comments start with –.

Any text between – and the end of the line will be ignored (will not be executed).

The following example uses a single-line comment as an explanation:

Example
–Select all:
SELECT * FROM Customers;

68
Q

Multi-line Comments

A

Multi-line comments start with /* and end with */.

Any text between /* and */ will be ignored.

The following example uses a multi-line comment as an explanation:

Example
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
69
Q

CREATE DATABASE statement

A

is used to create a new SQL database.

70
Q

CREATE DATABASE Syntax

A

CREATE DATABASE databasename;

71
Q

DROP DATABASE statement

A

is used to drop an existing SQL database

72
Q

DROP DATABASE Syntax

A

DROP DATABASE databasename;

**check it in the list of databases with the following SQL command: SHOW DATABASES;

73
Q

BACKUP DATABASE statement

A

is used in SQL Server to create a full back up of an existing SQL database.

74
Q

BACK UP STATEMENT Syntax

A

BACKUP DATABASE databasename

TO DISK = ‘filepath’;

75
Q

CREATE TABLE statement

A

is used to create a new table in a database.

76
Q

CREATE TABLE Syntax

A 
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);
77
Q

DROP TABLE statement

A

is used to drop an existing table in a database.

78
Q

DROP TABLE Syntax

A

DROP TABLE table_name;

79
Q

ALTER TABLE Statement

A
  • is used to add, delete, or modify columns in an existing table.
  • is also used to add and drop various constraints on an existing table.
80
Q

ALTER TABLE - ADD/ DROP/ ALTER(MODIFY) COLUMN

A

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD (xxx/vvv/vvv/ + COLUMN)column_name datatype;

81
Q

SQL constraints

A

are used to specify rules for data in a table

Tin hoc co so (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions