What is uncorrelated subquery? Given the salary column defined as VARCHAR(5), wh

Question

What is uncorrelated subquery?

Given the salary column defined as VARCHAR(5), what, if anything, is wrong with the following SQL statement? SELECT ename, salary * 1.05
FROM emp;

Explain how to use COMMIT within the MySQL transaction.

Which columns in a table should you consider indexing? What are other considerations that should be considered before creating the index?

The lead developer is insisting that you create a bitmapped index on the primary key of a table. Write a paragraph explaining why you would or would not grant his or her request. Provide a rationale for whatever position you choose.

Review this query.
SELECT CONCAT(FirstName, ' ', LastName) AS CustomerName,
Street, City, State, ZipPostalCode
FROM CUSTOMER
ORDER BY CustomerName;

Create a stored function named FirstNameFirst() so that the following query will generate the same output as the above query.

SELECT FirstNameFirst(FirstName, LastName) AS CustomerName,
Street, City, State, ZipPostalCode
FROM CUSTOMER
ORDER BY CustomerName;

You are the DBA of a development project. Your developers are arguing about whether to write a query using a join, a subquery, a correlated subquery, or a set operator. Each faction claims that one method performs better than the others. Write a brief synopsis that tells the developer how to prove whether one query performs better than another. Include the different factors that affect performance for queries.

What are some significant issues when using triggers? Be sure to explain your answers clearly, accurately, and concisely.

What are the regular preventive maintenance programs for MySQL databases?

Explanation / Answer

1)Uncorrelated sub query means the Outer and inner queries are independent of each other.
we can identify easily and mostly the sub queries involving u IN or NOT IN clause are good examples for these kind .
The inner query executes and then the outer query .  
Example:
EMP(EmpName,EmpNo,Job,mgr,sal,depto);
DEPT(deptno,dname,loc);
select * from emp where deptno in ( select deptno from dept where dname="NEW YORK" );

2)The salary column is declared as VARCHAR2(5),since it is a string variable you cannot get the calculated value as it with not number u end up with 0.
The salary should be declared as numeric entity. U can defined as SALARY NUMBER(5);
3)Simple add 'COMMIT' after any of the SQL commands (DML SELECT, INSERT, UPDATE or DELETE.   commands)statements in the transaction.WE use this command post the successful transaction ,the COMMIT command is issued to ensure that the changes to all involved tables will take effect.
Example:
START TRANSACTION;
SELECT @A:=Select MAX(salary) FROM emp WHERE deptno=2;
UPDATE Historytbl SET summary=@A WHERE deptno=2;
COMMIT;

4)Indexes makes the data retrieval faster.
The indexes are mainly created on frequently used columns and integrity constraints(referential and unique) exists on the column.
Create index on simple columns and built on one or few columns at least.It will be easy to scan if the indexes are not complex
Indexes should be created after loading the data into table.Table with indexes takes longer time than the table with out indexes to insert/delete or update.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.