Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
22 Cards in this Set
- Front
- Back
|
What is RDBMS?
|
Relational Data Base Management System.
A type of dbms that stores data in the form of related tables. |
|
|
What is normalization?
|
the process of organizing data to minimize redundancy
defining relationships between well-formed tables |
|
|
What is a stored procedure?
|
a named group of SQL statements that have been previously created and stored in the server database
stored procs reduce network traffic and improve performance |
|
|
What is a Trigger?
|
a trigger is a SQL procedure that initiates an action when an event occurs (INSERT, UPDATE or DELETE).
|
|
|
What is an Index?
|
a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently.
Indexes are one of the best ways to mprove perfomrance. A table scan happens when there is no index available to help a query. |
|
|
What is a cursor?
|
a database object used to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
|
|
|
Steps in using a cursor
|
1. Declare cursor
2. Open cursor 3. Fetch row from the cursor 4. Process fetched row 5. Close cursor 6. Deallocate cursor |
|
|
Wht is a Linked Server
|
allows remote data to be retrieved, joined and combined with local data
|
|
|
how to implement one to one relationships
|
a single table
|
|
|
how to implement one to many relationships
|
spliiting data into two tables with primary key and foreign key relationships
|
|
|
how to implement many to many relationships
|
using a junction table with the keys from both tables forming the composite primary key of the junction table
|
|
|
What is SQL Profiler
|
graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server
|
|
|
Which TCP/IP port does SQL Server run on? How can it be changed?
|
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number
|
|
|
Where are SQL server users names and passwords are stored in sql server?
|
They get stored in master db in the sysxlogins table.
|
|
|
What is identity?
|
(or AutoNumber) a column that automatically generates numeric values
|
|
|
Advantages of Stored Procedures
|
1. Can reduce network traffic boosting perfromance
2. Help promote code reuse 3. Can encapsulate logic - you can change sp code without affecting clients. 4. Provide better security to your data |
|
|
what is SQL injection?
|
SQL injection is a code injection technique, used to attack data driven applications, in which malicious SQL statements are inserted into an entry field for execution
|
|
|
What is the Having clause in SQL?
|
HAVING is only for conditions involving aggregates used in conjunction with the GROUP BY clause.
HAVING applies to groups. WHERE applies to rows. |
|
|
What is an aggregate function?
|
Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.
some common examples: AVG COUNT GROUPING MAX MIN SUM |
|
|
Deterministic Functions
|
Deterministic functions always return the same output when given identical inputs.
ISNULL is an example |
|
|
Nondeterministic Functions
|
Nondeterministic functions may return different results each time they are called with a specific set of input values.
GETDATE is nondeterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed. |
|
|
What is the difference between DELETE and TRUNCATE?
|
TRUNCATE removes all rows from a table without logging the individual row deletions.
DELETE statement can use a WHERE clause TRUNCATE TABLE is faster and uses fewer system and transaction log resources. If required the DELETE can be rolled back, TRUNCATE cannot. |
