DBA QUIZ 1

STRUCTURED QUERY LANGUAGE

• It is an industry-standard programming language used to insert, retrieve, modify, and delete data in a relational database.

• It also contains statements for defining and administering the objects in a database.

INSERT, UPDATE, RETRIEVE, CREATE, DELETE RECORDS

ACTIONS A DB ADMIN CAN DO USING SQL

RELATIONAL DATABASE

• is a database divided into logical units called tables, where tables are related to one another within the database.

• allows data to be broken down into logical, smaller, and manageable units for easier maintenance and better performance.

TABLES

• are collections of rows and columns, and are related to one another through common keys or fields in a relational database system

FIELD

• is a single characteristic, attribute or property that provides information about an object

RECORD

• is a complete set of all the fields/attributes combined together for a particular object

DATA DEFINITION, DATA MANIPULATION, DATA CONTROL

THREE CATEGORIES OF SQL

DATA DEFINITION LANGUAGE

• Commands used to define a database, including those for creating, altering, and dropping tables and establishing constraints.

• Sample of commands are CREATE, ALTER and DROP

DATA MANIPULATION LANGUAGE

• Commands are used for updating, inserting, modifying, and querying the data in the database

• Sample of commands are INSERT, UPDATE, DELETE and SELECT

DATA CONTROL LANGUAGE

• Commands that help a DBA control the database

• they include commands to grant or revoke privileges to access the database or particular objects within the database and to store or remove transactions that would affect the database.

CREATE DATABASE

• SQL COMMAND FOR CREATING A DATABASE

USE

• SQL COMMAND USED FOR SELECTING THE DATABASE TO USE.

SP_RENAME ‘OLD_NAME‘, ‘NEW_NAME‘

• SQL STATEMENT USED TO RENAME A DATABASE

DROP DATABASE

• SQL COMMAND USED TO DELETE A DATABASE

TABLES

• ARE DATABASE OBJECTS THAT STORES DATA IN A COLLECTION OF ROWS AND COLUMNS

• CAN HAVE UP TO 1042 COLUMNS

TABLE NAMES

• MUST START WITH ALPHABETIC CHARACTER AND CAN CONTAIN UNDERSCORES, @ SYMBOLS, # POUND SYMBOLS, AND NUMERALS.

• CAN HAVE UP TO 128 CHARACTERS

• CAN BE DELIMITED BY SPACES AND OTHER SYMBOLS

DATA TYPES

• IS AN ATTRIBUTE THAT SPECIFIES THE TYPE OF DATA THAT THE COLUMN CAN STORE

• IT CAN ALSO DECIDE THE OPERATIONS THAT CAN BE PERFORMED ON THE DATA OF THAT ATTRIBUTE.

EXACT NUMERIC DATA TYPES

• STORES EXACT NUMBERS SUCH AS INTERGERS, DECIMAL, OR MONETARY AMOUNT.

BIT

• STORES THREE VALUES: 0, 1, AND NULL

• HAS A RANGE OF 0-225 AND CONSUMES 1 BYTE OF STORAGE

SMALLINT

• STORES VALUES FROM -32,768 TO 32,768

• CONSUMES 2 BYTES OF STORAGE

INT

• STORES VALUES 2 ^ 31 TO 2 ^ 31 -1

• CONSUMES 4 BYTES OF STORAGE

BIGINT

• STORES VALUES FROM -2 ^ 63 TO 2 ^ 63-1

• CONSUMES 8 BYTES OF STORAGE

INT, BIGINT, SMALLINT, TINYINT

DATA TYPES THAT STORES INTEGER DATA

MONEY AND SMALLMONEY

STORES CURRENCY VALUES

APPROXIMATE NUMERIC DATA TYPES

• STORES FLOATING POINT NUMERIC DATA

• OFTEN USED IN SCIENTIFIC CALCULATIONS

DATE AND TIME DATA TYPES

• USED TO STORE DATE AND TIME DATA, AND THE DATE TIME OFFSET

DATETIME, SMALLDATETIME, DATE, TIME

EXAMPLE OF DATE AND TIME DATA TYPES

CHARACTER STRING DATA TYPES

• CONSISTS OF ANY COMBINATION OF LETTERS, SYMBOLS, AND NUMERIC CHARACTERS

• ALLOWS USERS TO STORE FIXED LENGTH OR VARIABLE-LENGHT DATA

UNICODE CHARACTER STRING DATA TYPES

• STORES EITHER FIXED LENGTH OR VARIABLE-LENGTH UNICODE CHARACTER DATA

BINARY STRING DATA TYPES

• STORES FIXED AND VARIABLE LENGTH BINARY DATA

NULL

• A VALUE THAT MAY BE ASSIGNED TO AN ATTRIBUTE WHEN NO OTHER VALUE APPLIES OR WHEN THE APPLICABLE VALUE IS UNKNOWN

• MEANS THAT NO ENTRY HAS BEEN MADE

PRIMARY KEY

• IS A FIELD OR A COMBINATION OF FIELDS THAT IDENTIFY A RECORD UNIQUELY

• IT IS A COLUMN OF A SET OF COLUMN THAT ARE UNIQUE

ONE PRIMARY KEY PER TABLE, MUST BE UNIQUE AND UNIQUELY IDENTIFIES EACH ROW, CANNOT BE NULL

RULES FOR PRIMARY KEYS

FOREIGN KEYS

• PROVIDES A WAY OF ENFORCING REFERENTIAL INTEGRITY WITHIN MYSQL SERVER.

• ENSURES THAT VALUES IN ONE TABLE MUST BE PRESENT IN ANOTHER TABLE

• GOVERNS THE LINK BETWEEN THE PARENT AND THE CHILD TABLE

PARENT TABLE

• TABLE BEING REFERENCED BY THE FOREIGN KEY

CHILD TABLE

• THE TABLE WITH THE FOREIGN KEY

REFERENTIAL INTEGRITY

• THE RULE PARENT-CHILD RELATIONSHIP ENFORCES

• A STATE IN WHICH ALL FOREIGN KEY VALUES IN THE DATABASE ARE VALID.

DEFAULT

• AN OPTIONAL VALUE TO A COLUMN

• THEY ARE USED TO PUT ANY VALUE FOR THE COLUMN FOR ANY ROW THAT IS INSERTED TO, WITHOUT EXPLICITLY SUPPLYING THE VALUE FOR THAT PARTICULAR COLUMN

IDENTITY COLUMN

• USED TO CREATE A COLUMN THAT CONTAINS SYSTEM-GENERATED SEQUENTIAL VALUES TO IDENTIFY EACH ROW INSERTED INTO A TABLE

IDENTITY SEED

• PROVIDES THE STARTING VALUE OF THE SEED

IDENTITY INCREMENT

• DEFINES HOW AN IDENTITY WILL INCREASE OR DECREASE WITH EACH NEW ROW ADDED TO A TABLE

DATA INTEGRITY

• REFERS TO THE OVERALL ACCURACY, CONSISTENCY, AND COMPLETENESS OF DATA.

• USUALLY IMPOSED DURING THE DATABASE DESIGN PHASE THROUGH THE USE OF STANDARD PROCEDURES AND RULES

• CAN BE MAINTAINED THROUGH THE USE OF VARIOUS ERROR-CHECKING METHODS AND VALIDATION PROCEDURES

DOMAIN, ENTITY, REFERENTIAL

CATEGORIES OF DATA INTEGRITY

DOMAIN INTEGRITY

• THIS INTEGRITY SPECIFIES A SET OF DATA VALUES THAT ARE VALID FOR A COLUMN

• CAN BE DEFINED BY DATA TYPE, FORMAT, DATA LENGTH, NULLABILITY, DEFAULT VALUE, AND RANGE OF ALLOWABLE VALUES

• ALSO KNOWN AS COLUMN INTEGRITY

ENTITY INTEGRITY

• THIS INTEGRITY REQUIRES ALL THE ROWS IN A TABLE TO HAVE A UNIQUE IDENTIFIER

• ENFORCED BY EITHER PRIMARY KEY OR UNIQUE CONSTRAINT

• ALSO KNOWN AS ROW INTEGRITY

CONSTRAINTS

• ARE BUSINESS LOGIC THAT THE DATABASE SERVER ENFORCES

• ARE USED TO SPECIFY RULES FOR THE DATA IN THE TABLE.

• ARE USED TO LIMIT THE DATA THAT GOES INTO THE TABLE TO ENSURE ACCURACY AND RELIABILITY OF THE DATA IN THE TABLE

• THEY PREVENT DELETION OF TABLES IF THERE ARE DEPENDENCIES

UNIQUE CONSTRAINTS

• ENSURES THAT THERE ARE NO DUPLICATE VALUES ENTERED INTO SPECIFID COLUMNS THAT ARE NOT A TABLE’S PRIMARY KEY

DEFAULT CONSTRAINTS

• ENABLES USERS TO DEFINES THE VALUE THAT WILL BE SUPPLIED FOR A COLUMN WHENEVER A USER FAILS TO ENTER A VALUE

CHECK CONSTRAINTS

• IS USED TO ENFORCE DOMAIN INTEGRITY BY RESTRICTING VALUES ALLOWED IN A COLUMN TO SPECIFIC VALUES

• IT CONTAINS A BOOLEAN EXPRESSION THAT CAUSES A DATABASE TO EVALUATE WHETHER THE VALUE OF AN INSERTED OR UPDATED RECORD MATCHES THE CRITERIA

DROP CONSTRAINT

• COMMAND USED TO DELETE A CONSTRAINT

SP_HELPCONSTRAINT

• COMMAND USED TO LIST AVAILABLE CONSTRAINTS IN A TABLE

SELECT

• RETRIVES INFORMATION FROM THE DATABASE

Selection

• the capability used to choose rows in a table that you wanted returned by a query

Projection

• the capability to choose the columns in a table that you want returned by your query

Join

• the capability to bring together the data that you stored in different tables by creating a link through a column that both tables share

Select Clause

• Specifies the columns to be displayed

From clause

• specifies the table containing the columns listed in the select clause

Keyword

• Refers to an individual SQL element

• ex: SELECT and FROM

Clause

• Is a part of an SQL statement

• ex: SELECT empmo,ename…

Statement

• Is a combination of two or more clauses

• ex: Select * From emp

“*“

• Used to display all columns of data in a table

Column Alias

• Renames a column heading

• Useful with calculations

AS

• Used to give column aliases

+

• Concatenation operator

Literal

• Is a character, expression, or number included in the SELECT list

DISTINCT

• Used to eliminate duplicate rows in the result

• Provides uniqueness across set of selected columns

WHERE clause

• Add restriction to the query and displays rows that meet the condtions

condition

• Is composed of column names, expressions, constants, and a comparison operator

BETWEEN

• An operator used to display rows based on a range of values

IN

• An operator used to test for values in a list

Like

• Used to select rows that match a character pattern

NULL operator

• Used to test for values that are null

Logical Operator

• Combines the result of two component conditions to produce a single result based on them or to invert the result of a single condition

AND

• Requires both conditions to be true

OR

• Requires either condition to be true

NOT

• Displays a record if the conditions is not true

Parentheses

• used to force priority in the rules of precedence

Order By

• used to sort rows

• specifies the order in which retrieved rows are displayed

ASC

• Default sorting order

• Sorts the result from lowest to highest or in dates, earliest to oldest

DESC

• Sorts the result from highest to lowest

Function

• Is a programming unit returning a single value, allowing values to be passed in as parameters

Single-row Functions

• These can be used to execute an operation on each row of a query.

• Can be used to execute the same operation for every row a query retrieves

Multiple-row Functions

• These functions manipulate groups of rows to give one result per group of rows

String Functions

• Used to perform operations on input string and return an output string

UPPER

• Converts string to upper-case

LOWER

• Converts string to lower-case

LEN

• Returns the number of characters of the specified string expression

LEFT

• Returns the part of a string starting at a specified number of characters from the left.

Right

• Returns part of a character string starting at a specified number of characters from the right

LTRIM

• Returns a character expression after removing the leading blanks on the left

RTRIM

• Returns a character expression after removing the leading blanks on the right

REPLACE

• Replaces all occurences of the the second given string expression in the first string expression with a third expression

REPLICATE

• Repeats a character expression for a specified number of times

SUBSTRING

• Returns part of a character, binary, text, or image expression based on the supplied length

CHARINDEX

• Returns the starting position of a string specified in the parameter

Numeric Functions

• These functions operates on values of number class datatype

• They return and accept just numeric values

ABS

• Returns the absolute value of the number

POWER

• Returns the value of the given expression to the specified power

CEILING

• Returns the smallest integer greater than, or equal to the given numeric expression

FLOOR

• Returns the largest integer less than or equal to, the given numeric expression