Shared Flashcard Set

Details

SQL
SQL stands for Structured Query Language
76
Computer Science
Not Applicable
06/14/2020
Click here to study/print these flashcards.

Create your own flash cards! Sign up here.

Additional Computer Science Flashcards

 

 

Cards

Term
However, to be compliant with the ANSI standard, they all support at least the major commands ____ in a similar manner.
 Definition
(such as SELECT, UPDATE, DELETE, INSERT, WHERE)
Term
Note: Most of the SQL database programs also have
 Definition
their own proprietary extensions in addition to the SQL standard!
Term
To build a web site that shows data from a database, you will need:
 Definition
An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
To use a server-side scripting language, like PHP or ASP
To use SQL to get the data you want
To use HTML / CSS to style the page
Term
RDBMS stands for
 Definition
Relational Database Management System.
Term
RDBMS is the basis for
 Definition
SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
Term
tables
 Definition
The data in RDBMS is stored in database objects
Term
A table is a collection of
 Definition
related data entries and it consists of columns and rows.
Term
The following SQL statement selects all the records in the "Customers" table:
 Definition
SELECT * FROM Customers;
Term
SQL keywords are NOT case sensitive:
 Definition
select is the same as SELECT
Term
Semicolon is the standard way to
 Definition
separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
Term
SELECT
 Definition
- extracts data from a database
Term
UPDATE
 Definition
- updates data in a database
Term
DELETE
 Definition
- deletes data from a database
Term
INSERT INTO
 Definition
- inserts new data into a database
Term
CREATE DATABASE
 Definition
- creates a new database
Term
ALTER DATABASE
 Definition
- modifies a database
Term
CREATE TABLE
 Definition
- creates a new table
Term
ALTER TABLE
 Definition
- modifies a table
Term
DROP TABLE
 Definition
- deletes a table
Term
CREATE INDEX
 Definition
- creates an index (search key)
Term
DROP INDEX
 Definition
- deletes an index
Term
Note: The WHERE clause is not only used in SELECT
 Definition
statement, it is also used in UPDATE, DELETE statement, etc.!
Term
The WHERE clause can be combined with
 Definition
AND, OR, and NOT operators.
Term
The AND operator displays a record if
 Definition
all the conditions separated by AND are TRUE.
Term
The OR operator displays a record if
 Definition
any of the conditions separated by OR is TRUE.
Term
The NOT operator displays a record if
 Definition
the condition(s) is NOT TRUE.
Term
The ORDER BY keyword is
 Definition
used to sort the result-set in ascending or descending order.
Term
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use
 Definition
the DESC keyword.
Term
The INSERT INTO statement is used to
 Definition
insert new records in a table.
Term
The first way specifies both the
 Definition
column names and the values to be inserted:
Term
A field with a NULL value is a
 Definition
field with no value.
Term
How to Test for NULL Values?
 Definition
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
Term
UPDATE Table
 Definition
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
Term
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
 Definition
DELETE FROM table_name;
Term
The following SQL statement deletes all rows in the "Customers" table, without deleting the table:
 Definition
DELETE FROM Customers;
Term
UPDATE Syntax
 Definition
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Term
IS NULL Syntax
 Definition
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
Term
IS NOT NULL Syntax
 Definition
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Term
INSERT INTO Syntax
 Definition
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Term
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. The INSERT INTO syntax would be as follows:
 Definition
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Term
ORDER BY Syntax
 Definition
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Term
AND Syntax
 Definition
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
Term
OR Syntax
 Definition
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Term
NOT Syntax
 Definition
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Term
WHERE Syntax
 Definition
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Term
SELECT DISTINCT Syntax
 Definition
SELECT DISTINCT column1, column2, ...
FROM table_name;
Term
SELECT Syntax
 Definition
SELECT column1, column2, ...
FROM table_name;
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;
Term
SQL Server / MS Access Syntax:
 Definition
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
Term
MySQL Syntax:
 Definition
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Term
Oracle Syntax:
 Definition
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Term
MIN() Syntax
 Definition
SELECT MIN(column_name)
FROM table_name
WHERE condition;
Term
MAX() Syntax
 Definition
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Term
COUNT() Syntax
The COUNT() function returns the number of rows that matches a specified criterion.
 Definition
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Term
AVG() Syntax
The AVG() function returns the average value of a numeric column.
 Definition
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Term
SUM() Syntax
The SUM() function returns the total sum of a numeric column.
 Definition
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Term
The LIKE operator 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:
 Definition
% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
Term
LIKE Syntax
 Definition
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Term
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
 Definition
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
Term
The following SQL statement selects all customers with a CustomerName starting with "a":
 Definition
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
Term
The following SQL statement selects all customers with a CustomerName ending with "a":
 Definition
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
Term
The following SQL statement selects all customers with a CustomerName that have "or" in any position:
 Definition
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
Term
The following SQL statement selects all customers with a CustomerName that have "r" in the second position:
 Definition
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
Term
The following SQL statement selects all customers with a CustomerName that does NOT start with "a":
 Definition
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';
Term
Wildcard Characters in SQL Server
 Definition
Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt
Term
Wildcard characters are used with the SQL LIKE operator.
 Definition
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Term
The SQL IN Operator
 Definition
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
Term
The SQL IN Operator(2)
 Definition
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Term
The SQL BETWEEN Operator
 Definition
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Term
he BETWEEN operator selects values
 Definition
within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Term
NOT BETWEEN Example
 Definition
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Term
BETWEEN with IN Example
 Definition
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
Term
BETWEEN Dates Example
 Definition
SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Term
SQL Aliases
 Definition
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 the query.
Term
Alias Column Syntax
 Definition
SELECT column_name AS alias_name
FROM table_name;
Term
Alias Table Syntax
 Definition
SELECT column_name(s)
FROM table_name AS alias_name;
Term
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
 Definition
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
Supporting users have an ad free experience!