SQL QSTS FOR QA TESTER

A table may have multiple foreign keys, and each foreign key can have a different parent table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.

Can a table have more than one foreign key defined???

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object

DDL Commands???

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency

DML commands???

Basic Difference. - Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). -Functions can have only input parameters for it whereas Procedures can have input/output parameters . -Functions can be called from Procedure whereas Procedures cannot be called from Function.

Explain the difference between function and stored procedure???

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; Example UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';

How do you change a value of the field???

The SQL syntax for ALTER TABLE Add Column is, ALTER TABLE "table_name" ADD "column_name" "Data Type"; Example: ALTER TABLE Customer ADD Gender char(1); -------------------------------------------- Table Customer --------------------------------------------- Column Name | Data Type ------------------------------------------- First_Name | char(50) Last_Name | char(50) Address |char(50) City |char(50) Country |char(25) Birth_Date |datetime

How to add column to a table???

CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); Now we want to create a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City. We use the following CREATE TABLE statement: Example CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

How to add record to a table???

-Create a table EmpDtl1 with some duplicate rows as shown below to understand different methods of delete duplicate rows. create table EmpDup(empid int,name varchar(20)) insert into EmpDup values(1,'Andy') insert into EmpDup values (1,'Andy') insert into EmpDup values(2,'Bill') insert into EmpDup values(2,'Bill') insert into EmpDup values (2,'Bill') insert into EmpDup values (3,'Chris') - Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below. select distinct * into #tmp From EmpDup delete from EmpDup insert into EmpDup select * from #tmp drop table #tmp

How to remove duplicate rows from a table???

TCL Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

TCL ???

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

TCL commands???

By default SQL Server listens on TCP port number 1433

What default TCP Port is Microsoft SQL Server running under???

By default SQL Server listens on TCP port number 1433

What default TCP Port is MySQL running under???

????

What default TCP Port is Oracle SQL Server running under???

-Data Definition Language (DDL) statements are used to define the database structure or schema. -Data Manipulation Language (DML) statements are used for managing data within schema objects. -Data Control Language (DCL) statementsSome examples: GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command

What does DDL, DML, and DCL stand for???

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

What does RDBMS stand for???

TCP/IP (Transmission Control Protocol/Internet Protocol) is the basic communication language or protocol of the Internet. It can also be used as a communications protocol in a private network (either an intranet or an extranet).

What is TCP???

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft® SQL Server™. You can capture and save data about each event to a file or SQL Server table to analyze later.

What is a SQL Profiler???

Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.

What is a foreign key???

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records ***It must contain a unique value for each row of data. ***It cannot contain null values.

What is a primary key???

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs.

What is a stored procedure???

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

What is a trigger???

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data.

What is cursor???

No DML triggers will be fired. The operation cannot be rolled back. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back(undone).

What is the difference between DELETE, TRUNCATE, and DROP commands???

Though both inner and outer joins include rows from both tables when the match condition is successful, they differ in how they handle a false match condition. Inner joins don't include non-matching rows; whereas, outer joins do include them.

What is the difference between an inner join and an outer join???

The main purpose of a primary key is to implement a relationship between two tables in a relational database; - the primary key is the "target" which a foreign key can reference.

What is the main role of a primary key in a table???

A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

What is view???

-Inner Join Equi-join Natural Join -Outer Join Left outer Join Right outer join Full outer join -Cross Join -Self Join

What types of joins are possible with database server???

select * from tbl_name order by id desc limit N;

Write a code to select the last N rows from a table???

SELECT * FROM (your ordered query) alias_name WHERE rownum <= Rows_to_return ORDER BY rownum; Example: For example, if you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query: SELECT * FROM (select * from suppliers ORDER BY supplier_name) suppliers2 WHERE rownum <= 3 ORDER BY rownum;

Write a code to select the top N rows from a table???