1. What is the most complex SQL quary you have written?
2. Write a SQL statement to retrieve all the data from a table called ‘class’.
3. Write a SQL statement to retrieve all the data from a table called 'qaclass06'.
4. Display class name and class location from the table 'class'.
5. Display Last Name, First Name, ID and Phone No from 'qaclass06'.
6. Insert 4 Rows and data in 'class'.
7. Insert one row of data in 'qaclass 06'.
8. Write the syntax for the following: drop, delete, and truncate.
9. Join three tables using alias.
Subscribe to:
Post Comments (Atom)
7 comments:
Murari Naral
1. What is the most complex SQL query you have written?
In one of my previous project, there were hundreds of tables. I found out that for some reason one of the table had data discrepancy with missing some required values including primary value. When I attempted to run my query the output was horrible. I researched and decided to delete the rows. For that I had to match related columns to one table to another tables even using sub-queries. I had to create many temp tables. I had to match null values also. And, interestingly, I came across the situation a few times that I had to match even string to string values with two or more different tables and different users had entered data in mixed cases (upper and lower).
2. Retrieve all data from CLASS
SELECT * FROM CLASS;
3. Retrieve all data from QACLASSFALL06.
SELECT * FROM QACLASSFALL06;
4. Display class name and clas location from class.
SELECT CLASS_NAME, LOCATION FROM CLASS;
5. Display last name, first name, ID and cell phone from QACLASSFALL06.
SELECT L_NAME, F_NAME, ID, C_PHONE FROM QACLASSFALL06;
6. Insert 4 rows of data in CLASS.
Classid, classname, classloc, classadd
INSERT INTO CLASS
VALUES(123, ‘SQA’,‘chantilly’, 4 );
INSERT INTO CLASS
VALUES(124, ‘SQL’,‘chantilly’, 3 );
INSERT INTO CLASS
VALUES(125, ‘UNIX’,‘chantilly’, 2 );
INSERT INTO CLASS
VALUES(126, ‘WINDOWS’,‘chantilly’, 1 );
7. Insert 1 row of data in QACLASSFALL06.
INSERT INTO QACLASSFALL06
VALUES(6,’nepal’,’prakash’,’6001 arlington bv’,7038644141, 7038644141);
8. Write the syntax for the following
Drop, delete, truncate
Drop table QACLASSFALL06;
Delete from QACLASSFALL06 where ssn = 1112223333;
Truncate table QACLASSFALL06;
9. Join 3 tables using alias. (Tables – employee, dept, manager)
SELECT
E.FNAME ,
E.LNAME,
E.HIRE_DT,
E.REV_DT,
D.DEPT_NAME,
M.MANAGER
FROM EMPLOYEE E, DEPT D, MANAGER M
WHERE
E.DEPT_NO = D.DEPT_NO AND
D.DEPT_NO = M.DEPT_MGR AND
REVIEW_DATE BETWEEN ’01-DEC-06 AND ’31-JAN- 07’;
This query will display for following:
FNAME LNAME HIRE_DT REV_DT DEPT_NAME MANAGER
-------------------------------------------------------------------------------------------
LARRY SMITH 07-DEC-06 07-DEC-07 PAYROLL JOHN CLARK
Yuba R Dhakal 01/23/07
What is the most complex SQL quary you have written?
In my previous project I had written SQL quary where I had to look the data from the different tables of database by using alias.
To retrieve all the data from ‘class’:
SQL> select *from clsss;
Retrieve all the data from qaclass06 :
SQL> select *from qaclass06;
Display class name and class location from class :
SQL> select Id, className from class;
Display LName, FName, ID and Phone No from qaclass06.
SQL> select Lname,Fname,Id, Phone_no from qaclass06;
Insert 4 Rows and data in class.
SQL> insert into class
Values(101,’QA’,’Virginia’,’1245 chantily’);
SQL>insert into class
Values(102,’elc’,’Ohio’,’236 Ohio Rd’);
SQL>insert into class
Values(103,’mgt’,’chicago’,’shany Rd’);
Insert one row of data in qa class 06
SQL>insert into qaclass06
Values( 111,’Dhakal’,’Yubaraj’,7033025650,’Glebe Rd VA’);
Write the syntax for the following: drop, delete, and truncate.
For Drop: SQL>drop table qaclass06;
For delete: SQL>delete table
For truncate: SQL> truncate table qaclass06;
Join three tables using alias.
SQL> SELECT A.F_NAME,A.L_NAME,B.ADDRESS,C.POSITION FROM DEPT A,EMPLOYEE B ,LOCATION C, AND A.SSN = B.SSN AND B.DEPTNO = C.DEPTNO;
1. The scenario was as follows:
I was working as a QA tester. I had to perform backend testing. I wrote SQL queries to retrieve data from database. The data of the application software was rather huge and stored haphazardly in the database. I had to match data to the GUI interface. I was trying to retrieve the particular information of the person in the data base but I was getting hard time to get the data. Later on, all team members were having difficulties to retrieve data as well. We testers reported the problem to our QA leads. Thereafter after a hard work by the database management team reduce the whole data and tabulated in two. So we again started to test resolved data, but we couldn’t still pullout the data from the data base and match. We reported the same problem again to our lead. There was no really walk through around. No options, rather than to collapse the entire database. It was really a tragedy and a big defect. In my work history this was a terrible scenario I had before.
2. Select * from class;
3. Select * from QA_class_Falls_06;
4. Select class name, class location, from class;
5. Select Lname, Fname,ID, Cellphone, from QA_Falls_06;
6. Insert into class(4,’ELC’,’chantilly’, ‘fairfax’);
(5,’ELt’,’lorton’, ‘fairfax’);
(6,’MIT’,’Vienna’, ‘Louden’);
(7,’CAT’,’york’, ‘georges’);
7. Drop table class;
8. Delete from table class;
9. truncate table class;
10. Select A. fname, A.lastname, A.dept no, C. addresss, B. position from dept A, Location C, employee B, where C. address =’7623 lorton road’ and A.SSN=C.SSN and A. dept = B dept;
1. What is the most complex SQL query you have written?
SQL>I have written queries to join multiple tables using alias where some table did not have common field. I had to use another table with having common field on at least two tables and to filter data I used Boolean operators (AND, OR & NOT) to retrieve specific data from the databases.
2. Write a SQL statement to retrieve all the data from a table called ‘class’.
SQL>Select * from class;
3. Write a SQL statement to retrieve all the data from a table called 'qaclass06'.
SQL>Select * from qaclass06;
4. Display class name and class location from the table 'class'.
SQL>Select class_name, class_loc from class;
5. Display Last Name, First Name, ID and Phone No from 'qaclass06'.
SQL>Select l_name, f_name, id, phone_no from qaclass06;
6. Insert 4 Rows and data in 'class'.
SQL>Insert into class values (111, ‘qa’,‘falls church’, 4 );
SQL>Insert into class values (112, ‘mgt’,‘falls church’, 6 );
SQL>Insert into class values (113, ‘qa’,‘arlington’, 4 );
SQL>Insert into class values (111, ‘elc’,‘falls church’, 3 );
7. Insert one row of data in 'qaclass 06'.
SQL>Insert into qaclass06 values (111,’subash’,’singh’,’6001 Arlington bv’,7033890683, 7033890683);
8. Write the syntax for the following: drop, delete, and truncate.
SQL>Alter table emp drop column salary;
SQL>Delete from emp where position=’temp’;
SQL>Truncate table emp;
9. Join three tables using alias.
SQL>Select x.id,x.l_name,x.cell_phone,y.class_loc from qaclass06 x,class y where class_name=’qa’ and x.id=y.id;
Home Work
Rajan Bhandary
Jan 23,2007
1 . What is the most complex SQL you have written?
Join three tables using alias was one of the complex SQL I have written so far.
2. Retrive all the data from class
Select*from class;
3. Retrive all the data from qaclassfall06
Select*from qaclassfall06;
4. Display class name and class location from class
Select class name,class location from qaclassfall06;
5. Display last name, first name, id and cell phone from qaclassfall06
Select lastname, firsname, id, and cell phone from qaclassfall06;
6. Insert 4 rows of data in lass
Insert into qaclassfall06
values (101,’bhandary’,’rajan’,’9266 piney branch rd’,3014343505,2405331291,11);
values(102, jack’, ‘harry’, ‘fairfax’, 313333442,3011010001, 12);
values (103, ‘aryal’,’krishna’, ‘arlington’ 70345454444,3014343333,13);
values (104, ‘yubaraj’, “khanal’,’fairoaks’,3013332323,2403333333,14,);
7 Insert into one row of data in qaclassfall06
Insert into qaclassfall06
Values
8 Write the syntax for the following drop, delete, and truncante.
Drop
Drop table classfall06;
Delete
Delete table qaclassfall06
Truncan table qaclass06;
9. Join three tables using alias.
select a.,filesname a.,last name, dept number, b address, from dept b.
Krishna Aryal
Question # 1: What is the most complex SQL Queries you have written?
In my long experience of QA Career, I have a non forgettable experience in writing of SQL queries. The scenario was, I had to pull out four different data from 3 different tables. All the data were independent and there were no common factors to each other. I tried my best couple of times, but I couldn’t get through on it. I asked my lead he also tried many times to teach me, but didn’t get through this problem. He asked a developer who was his good friend. He came and pulled the data easily with taking one more common table to get all the data. We laughed watching his technique that how easily he got the data. Therefore, it was the complex SQL Queries for me and is unforgettable.
Question # 2: Retrieve all the data from Class?
SQL< Select * from tab; (enter)
SQL< Select * from Class; (enter)
Question # 3: Retrieve all the data from QAClassFall06?
SQL< Select * from Tab; (enter)
SQL< Select * from QAClassFall06; (enter)
Question # 4: Display Class name and Class location from Class?
SQL< * from Tab; (enter)
SQL< Select Class name, Class location from Class; (enter)
Question # 5: Display Last name, First name, ID and Cell phone from QAClassFall06?
SQL< Select * from Tab; (enter)
SQL< Select Last name, First name, ID, Cell phone from QAClassFall06;
(Enter)
Question # 6: Insert four rows of data in Class?
SQL< insert into Class values
(6, ‘QA’, ‘Virginia’, ‘Chantilly’);
Question # 7: Insert one row of data in QAClassFall06?
SQL< insert into QAClassFall06 values
(6);
Question # 8: Write the Syntax for the following:
Drop, Delete, Truncate
SQL< drop table Class;
SQL< delete Fname from Class;
SQL< truncate from Class;
Question # 9: Join 3 tables using alias?
Tables Datas
Finance / Fname, Laname, ID No., SSN, Salary
/
Personnel Dept / Position, SSN, Division, Dept no.
/
Procurement / Fname, Lname, SSN, Section
---------------------------------------------------------------------------------------------------
Ans: Here, I have given alias as A, B and C for the tables respectively.
SQL< Select A. Fname, B. Position, C. Lname
Where
A. Fname = C. Fname
And
A. SSN = B. SSN = C. SSN ( this is my personal syntax)
Question # 1: What is the most complex SQL Queries you have written?
In my previous project I had written the SQL quary from the different tables of database by using alias .
Question # 2: Retrieve all the data from Class?
SELECT * FROM CLASS ;
Question # 3: Retrieve all the data from QAClassFall06?
SQL< Select * from Tab; (enter)
SQL< Select * from QAClassFall06; (enter)
Question # 4: Display Class name and Class location from Class?
SELECT CLASS _NAME ,CLASS_ LOCATION FROM TABLE ‘ CLASS
Question # 5: Display Last name, First name, ID and Cell phone from QAClassFall06?
;
Question # 6: Insert four rows of data in Class?
SQL< insert into Class values
(6, ‘QA’, ‘Virginia’, ‘Chantilly’);
Question # 7: Insert one row of data in QAClassFall06?
SQL< insert into QAClassFall06 values
(6);
Question # 8: Write the Syntax for the following:
Drop, Delete, Truncate
SQL< drop table Class;
SQL< delete Fname from Class;
SQL< truncate from Class;
Question # 9: Join 3 tables using alias?
Tables Datas
Finance / Fname, Laname, ID No., SSN, Salary
/
Personnel Dept / Position, SSN, Division, Dept no.
/
Procurement / Fname, Lname, SSN, Section
---------------------------------------------------------------------------------------------------
Ans: Here, I have given alias as A, B and C for the tables respectively.
SQL< Select A. Fname, B. Position, C. Lname
Where
A. Fname = C. Fname
And
A. SSN = B. SSN = C. SSN ( this is my personal syntax)
Post a Comment