Please Answer and explain so I can Learn as well! Use your knowledge of MySql to
ID: 3717927 • Letter: P
Question
Please Answer and explain so I can Learn as well!
Use your knowledge of MySql to answer the following
1. Which is the default order of sort in ORDER BY clause?
2. What is a candidate key?
3. How can we get the number of records or rows in a table?
4. What are the DDL, DML, TCL statements ?
5. What kind of variable is @cnt ?
SET @cnt = 3;
6. What is the value printed on third line ?
SET @num = 10;
SET @num = @num + 1;
SELECT (@num = 0),@num = @num + 1;
7. What is the main difference between While and Repeat loop ?
8. What is a cursor and what are the operations you can do with a cursor ?
9. Write the Create table script to with Primary and Foreign Key relationships.
Customer
--------
CustomerID
CustomerName
CustomerAddress1
City
State
Zip
Product
-----------
Product_ID
Product_Name
QtyInHand
Order
----------
OrderID
OrderQty
ProductID
CustomerID
Explanation / Answer
/*** Answering Initial 5 questions ****/
1.) default order of sort in ORDER BY clauses ? is Ascending . If you want to sort the the data in descending order then you have to mention it explicitly. for example
select * from Customer order by salary.
it will sort the data of customer table in ascending order . If you want to sort in descending order then use below
select * from Customers order by salary desc;
2.) Candidate Key:
A candidate key can be any column or combination of column that can qualify as unique key in the database. In a table there can be many candidate key but there can be only one primary key.So candidate key are the one which fullfil the requirements of being unique and not null in the table.
3.)Number of rows in the table:
select count(*) from tableName;
4.) DDL(Data Definition Language): Sql commands which can be used to define the database schema and can be used to create and modify the structure of the database objects in the database. For example
Create, Drop, Alter, truncate, rename etc commands
DML(Data Manipulation Language): These commands are used to modify the data present in the table of the database. For Example: Select, Insert, Update and Delete commands
TCL (Transaction Control Language): this deals transactions with in the database. like Commit, Rollback etc commands
5.) set @cnt = 3; This is used to create a temporary variable in your mysql commands.
here we have set cnt as 3 . Now we can use cnt in our query like
select @cnt:=@cnt + 1 as rowNumber, rowId from tableName where condition
So here we have incremented @cnt each time and printed as rowNumber ..