Analytics MIS 301 Rock
what two wildcards are used in conjunction with the LIKE operator
% and _
record
=each individual entry that exists in the table
always use -- ---- when looking for NULL values
IS NULL
what operator does mySQL use instead of SELECT TOP?
LIMIT
_ represents....
a single character
what does the IN operator do?
allow you to specify multiple values within a WHERE clause
how does the ORDER keyword sort by default?
ascending order
why was the HAVING clause added?
because WHERE didn't work with aggregate functions
how are each table identified?
by a name
what is the difference between [charlist] and [!charlist]
charlist=characters to find !charlist=characters not to find
define table
collection of related data entries consisting of columns and rows
define field
column in a table that is designed to maintain specific information about every record in the table
JOIN
combines rows from two or more tables based on a common column between them
UNION
combines the result-set of two or more SELECT statements
create database
creates a new database
create table
creates a new table
create index
creates an index (search key)
Define RDBMS
database program that provides the basis for SQL and all modern database systems
drop table
deletes a table
drop index
deletes an index
delete
deletes data from a database
does the UNION operator use distinct or all values
distinct
select
extracts data from database
what are tables broken up into?
fields
what are SQL alias used to do?
give tables, column, and rows temporary names
why use select distinct statement?
if you have a column with duplicating values and you only need to see the different or distinct ones
types fo SQL JOINS
inner join, left outer join, right outer join, and full outer join
insert into
inserts new data into a database
alter database
modifies a database
alter table
modifies a table
are SQL keywords case sensitive?
no
do all database systems support SELECT TOP?
no
self JOIN
regular join, but a table is joined with itself
What do the MIN() and MAX() functions do?
return the smallest or largest value of a selected column
what does the COUNT() function do?
returns the number of rows that match a specific criteria
what does the SUM() function do?
returns the total sum of a numeric column
what is the LIKE operator used for?
search for a specified pattern in a column
if you wanted to know the number of distinct cities, what would your command?
select count (distinct city) from customers
INNER JOIN
selects records that have matching values in both tables
BETWEEN operator
selects values within a range (numbers, text, or dates)
use of semicolons in SQL?
separates each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to server
SQL requires --- --- around text values
single quotes
what is the function of the SQL ORDER keyword?
sorts the result-set in ascending or descending order
Define SQL
standard language for accessing and manipulating databases
where is data in RDBMS stored?
tables
how are most actions you need to perform on a database done?
through SQL statements
what are the AND and OR operators used for?
to filter records based on more than one condition
what is the WHERE clause used for?
to filter records or extract only the records that fulfill a specified condition
what is the SELECT TOP clause used for?
to specify the number of records to return
update
updates data in a database
what is a wildcard character?
used to substitute any other character(s) in a string
GROUP BY
used with aggregate functions to group the result-set by one or more columns
how do you test for NULL values?
using the IS NULL and IS NOT NULL operators
Is SQL an ANSI standard?
yes
can the % and _ wildcards be used in combination?
yes
can you combine AND, OR, and NOT?
yes
can you join more than two tables?
yes
does a database often contain more than one table?
yes, one or more is most common
% represents....
zero, one, or multiple character
what can the WHERE clause be combined with?
AND, OR, NOOT operators
what does the AVG() function do?
RETURNS THE AVERAGE VALUE OF A NUMERIC COLUMN
what operator does oracle use instead of SELECT TOP?
ROWNUM
RDBMS
Relational Database Management System
FULL JOIN
Returns all records when there is a matching either table records
what does SQL stand for?
Structured Query Language
should numeric fields be enclosed in quotes?
`no
what is a NULL value?
a field with no value, a field in the table that was left blank during record creation