Collection of SQL queries with Answer and Output Set 3

Here is a collection or a list of 38 SQL Queries with Answers as well as output. You can write your answer at the text box below each query any time you can see the table structure by clicking on Table Structure. And check your Answer by clicking on Answer. You can test your Skill in SQL. You can also go for an online Quiz in SQL in one of my previous posts: Click here for Quiz. More queries will be added to this post within few days, visit again!!!

Happy learning!!!
Carry on....
You can also share your queries in this site. Use this Link to share your part with the visitors like you.

SQL Query collection: Set1 Set2 Set3 Set 4

Below is the Table Structure using which you have to form the queries:


1) Who is the highest paid C programmer?

Table Structure

Write your answer hereAnswer

SELECT * FROM PROGRAMMER
WHERE SALARY=(SELECT MAX(SALARY)
FROM PROGRAMMER
WHERE PROF1 LIKE C OR PROF2 LIKE C)

2) Who is the highest paid female cobol programmer?

Table Structure

Write your answer hereAnswer

SELECT * FROM PROGRAMMER
WHERE SALARY=(SELECT MAX(SALARY)
FROM PROGRAMMER
WHERE (PROF1 LIKE COBOL OR PROF2 LIKE COBOL))
AND SEX LIKE F

3) Display the name of the HIGEST paid programmer for EACH language (prof1)

Table Structure

Write your answer hereAnswer

SELECT DISTINCT NAME, SALARY, PROF1
FROM PROGRAMMER
WHERE (SALARY,PROF1) IN (SELECT MAX(SALARY),PROF1
FROM PROGRAMMER
GROUP BY PROF1)

4) Who is the LEAST experienced programmer?

Table Structure

Write your answer hereAnswer

SELECT FLOOR((SYSDATE-DOJ)/365) EXP,NAME
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOJ)/365) = (SELECT MIN(FLOOR((SYSDATE-DOJ)/365))
FROM PROGRAMMER)

5) Who is the MOST experienced programmer?

Table Structure

Write your answer hereAnswer

SELECT FLOOR((SYSDATE-DOJ)/365) EXP,NAME,PROF1,PROF2
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOJ)/365) = (SELECT MAX(FLOOR((SYSDATE-DOJ)/365))
FROM PROGRAMMER)
AND (PROF1 LIKE COBOL OR PROF2 LIKE COBOL)

6) Which language is known by ONLY ONE programmer?

Table Structure

Write your answer hereAnswer

SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN
(SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN
(SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1;

7) Who is the YONGEST programmer knowing DBASE?

Table Structure

Write your answer hereAnswer

SELECT FLOOR((SYSDATE-DOB)/365) AGE, NAME, PROF1, PROF2
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOB)/365) = (SELECT MIN(FLOOR((SYSDATE-DOB)/365))
FROM PROGRAMMER
WHERE PROF1 LIKE DBASE OR PROF2 LIKE DBASE)

8) Which institute has MOST NUMBER of students?

Table Structure

Write your answer hereAnswer

SELECT SPLACE
FROM STUDIES
GROUP BY SPLACE
HAVING COUNT(SPLACE)= (SELECT MAX(COUNT(SPLACE))
FROM STUDIES GROUP BY SPLACE)

9) Who is the above programmer?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE PROF1 IN (SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN (SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN (SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1))
UNION
SELECT NAME
FROM PROGRAMMER
WHERE PROF2 IN (SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN (SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN (SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1))

10) Which female programmer earns MORE than 3000/- but DOES NOT know C, C++, Oracle or Dbase?

Table Structure

Write your answer hereAnswer

SELECT * FROM PROGRAMMER
WHERE SEX LIKE F
AND SALARY >3000
AND (PROF1 NOT IN(C,C++,ORACLE,DBASE)
OR PROF2 NOT IN(C,C++,ORACLE,DBASE))

11) Which is the COSTLIEST course?

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
WHERE CCOST = (SELECT MAX(CCOST) FROM STUDIES)

12) Which course has been done by MOST of the students?

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
GROUP BY COURSE
HAVING COUNT(COURSE)= (SELECT MAX(COUNT(COURSE))
FROM STUDIES
GROUP BY COURSE)

13) Display name of the institute and course Which has below AVERAGE course fee?

Table Structure

Write your answer hereAnswer

SELECT SPLACE,COURSE
FROM STUDIES
WHERE CCOST < (SELECT AVG(CCOST) FROM STUDIES)

14) Which institute conducts COSTLIEST course?

Table Structure

Write your answer hereAnswer

SELECT SPLACE
FROM STUDIES
WHERE CCOST = (SELECT MAX(CCOST) FROM STUDIES)

15) Which course has below AVERAGE number of students?

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
HAVING COUNT(NAME)<(SELECT AVG(COUNT(NAME))
FROM STUDIES
GROUP BY COURSE)
GROUP BY COURSE;

16) Which institute conducts the above course?

Table Structure

Write your answer hereAnswer

SELECT SPLACE
FROM STUDIES
WHERE COURSE IN (SELECT COURSE
FROM STUDIES
HAVING COUNT(NAME) < (SELECT AVG(COUNT(NAME))
FROM STUDIES
GROUP BY COURSE)
GROUP BY COURSE);

17) Display names of the course WHOSE fees are within 1000(+ or -) of the AVERAGE fee.

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
WHERE CCOST < (SELECT AVG(CCOST)+1000 FROM STUDIES)
AND CCOST > (SELECT AVG(CCOST)-1000 FROM STUDIES)

18) Which package has the HIGEST development cost?

Table Structure

Write your answer hereAnswer

SELECT TITLE,DCOST
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE)

19) Which package has the LOWEST selling cost?

Table Structure

Write your answer hereAnswer

SELECT TITLE,SCOST
FROM SOFTWARE
WHERE SCOST = (SELECT MIN(SCOST) FROM SOFTWARE)

20) Who developed the package, which has sold the LEAST number of copies?

Table Structure

Write your answer hereAnswer

SELECT NAME,SOLD
FROM SOFTWARE
WHERE SOLD = (SELECT MIN(SOLD) FROM SOFTWARE)

21) Which language was used to develop the package WHICH has the HIGEST sales amount?

Table Structure

Write your answer hereAnswer

SELECT DEV_IN,SCOST
FROM SOFTWARE
WHERE SCOST = (SELECT MAX(SCOST) FROM SOFTWARE)

22) How many copies of the package that has the LEAST DIFFRENCE between development and selling cost were sold?

Table Structure

Write your answer hereAnswer

SELECT SOLD,TITLE
FROM SOFTWARE
WHERE TITLE = (SELECT TITLE
FROM SOFTWARE
WHERE (DCOST-SCOST)=(SELECT MIN(DCOST-SCOST) FROM SOFTWARE))

23) Which is the COSTLIEAST package developed in PASCAL?

Table Structure

Write your answer hereAnswer

SELECT TITLE
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST)
FROM SOFTWARE
WHERE DEV_IN LIKE PASCAL)

24) Which language was used to develop the MOST NUMBER of package?

Table Structure

Write your answer hereAnswer

SELECT DEV_IN FROM SOFTWARE
GROUP BY DEV_IN
HAVING MAX(DEV_IN) = (SELECT MAX(DEV_IN) FROM SOFTWARE)

25) Which programmer has developed the HIGEST NUMBER of package?

Table Structure

Write your answer hereAnswer

SELECT NAME FROM SOFTWARE
GROUP BY NAME
HAVING MAX(NAME) = (SELECT MAX(NAME) FROM SOFTWARE)

26) Who is the author of the COSTLIEST package?

Table Structure

Write your answer hereAnswer

SELECT NAME,DCOST
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE)

27) Display names of packages WHICH have been sold LESS THAN the AVERAGE number of copies?

Table Structure

Write your answer hereAnswer

SELECT TITLE
FROM SOFTWARE
WHERE SOLD < (SELECT AVG(SOLD) FROM SOFTWARE)

28) Who are the female programmers earning MORE than the HIGEST paid male programmers?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE SEX LIKE F
AND SALARY > (SELECT(MAX(SALARY))
FROM PROGRAMMER
WHERE SEX LIKE M)

29) Which language has been stated as prof1 by MOST of the programmers?

Table Structure

Write your answer hereAnswer

SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 = (SELECT MAX(PROF1)
FROM PROGRAMMER)

30) Who are the authors of packages, WHICH have recovered MORE THAN double the development cost?

Table Structure

Write your answer hereAnswer

SELECT NAME distinct
FROM SOFTWARE
WHERE SOLD*SCOST > 2*DCOST

31) Display programmer names and CHEAPEST package developed by them in EACH language?

Table Structure

Write your answer hereAnswer

SELECT NAME,TITLE
FROM SOFTWARE
WHERE DCOST IN (SELECT MIN(DCOST)
FROM SOFTWARE
GROUP BY DEV_IN)

32) Who is the YOUNGEST male programmer born in 1965?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE DOB=(SELECT (MAX(DOB))
FROM PROGRAMMER
WHERE TO_CHAR(DOB,YYYY) LIKE 1965)

33) Display language used by EACH programmer to develop the HIGEST selling and LOWEST selling package.

Table Structure

Write your answer hereAnswer

SELECT NAME, DEV_IN
FROM SOFTWARE
WHERE SOLD IN (SELECT MAX(SOLD)
FROM SOFTWARE
GROUP BY NAME)
UNION
SELECT NAME, DEV_IN
FROM SOFTWARE
WHERE SOLD IN (SELECT MIN(SOLD)
FROM SOFTWARE
GROUP BY NAME)

34) Who is the OLDEST female programmer WHO joined in 1992

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE DOJ=(SELECT (MIN(DOJ))
FROM PROGRAMMER
WHERE TO_CHAR(DOJ,YYYY) LIKE 1992)

35) In WHICH year where the MOST NUMBER of programmer born?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT TO_CHAR(DOB,YYYY)
FROM PROGRAMMER
WHERE TO_CHAR(DOJ,YYYY) = (SELECT MIN(TO_CHAR(DOJ,YYYY))
FROM PROGRAMMER)

36) In WHICH month did MOST NUMBRER of programmer join?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT TO_CHAR(DOJ,MONTH)
FROM PROGRAMMER
WHERE TO_CHAR(DOJ,MON) = (SELECT MIN(TO_CHAR(DOJ,MON))
FROM PROGRAMMER)

37) In WHICH language are MOST of the programmers proficient?

Table Structure

Write your answer hereAnswer

SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1))
FROM PROGRAMMER
GROUP BY PROF1)
OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2))
FROM PROGRAMMER
GROUP BY PROF2)
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1))
FROM PROGRAMMER
GROUP BY PROF1)
OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2))
FROM PROGRAMMER
GROUP BY PROF2)

38) Who are the male programmers earning BELOW the AVERAGE salary of female programmers?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE SEX LIKE M
AND SALARY < (SELECT(AVG(SALARY))
FROM PROGRAMMER
WHERE SEX LIKE F)

SQL Query collection: Set1 Set2 Set3 Set 4

Read More..

All the News thats Fit to Read A Study of Social Annotations for News Reading

Posted by Chinmay Kulkarni, Stanford University Ph.D candidate and former Google Intern, and Ed H. Chi, Google Research Scientist

News is one of the most important parts of our collective information diet, and like any other activity on the Web, online news reading is fast becoming a social experience. Internet users today see recommendations for news from a variety of sources; newspaper websites allow readers to recommend news articles to each other, restaurant review sites present other diners’ recommendations, and now several social networks have integrated social news readers.

With news article recommendations and endorsements coming from a combination of computers and algorithms, companies that publish and aggregate content, friends and even complete strangers, how do these explanations (i.e. why the articles are shown to you, which we call “annotations”) affect users selections of what to read? Given the ubiquity of online social annotations in news dissemination, it is surprising how little is known about how users respond to these annotations, and how to offer them to users productively.

In All the News that’s Fit to Read: A Study of Social Annotations for News Reading, presented at the 2013 ACM SIGCHI Conference on Human Factors in Computing Systems and highlighted in the list of influential Google papers from 2013, we reported on results from two experiments with voluntary participants that suggest that social annotations, which have so far been considered as a generic simple method to increase user engagement, are not simple at all; social annotations vary significantly in their degree of persuasiveness, and their ability to change user engagement.

News articles in different annotation conditions

The first experiment looked at how people use annotations when the content they see is not personalized, and the annotations are not from people in their social network, as is the case when a user is not signed into a particular social network. Participants who signed up for the study were suggested the same set of news articles via annotations from strangers, a computer agent, and a fictional branded company. Additionally, they were told whether or not other participants in the experiment would see their name displayed next to articles they read (i.e. “Recorded” or “Not Recorded”).

Surprisingly, annotations by unknown companies and computers were significantly more persuasive than those by strangers in this “signed-out” context. This result implies the potential power of suggestion offered by annotations, even when they’re conferred by brands or recommendation algorithms previously unknown to the users, and that annotations by computers and companies may be valuable in a signed-out context. Furthermore, the experiment showed that with “recording” on, the overall number of articles clicked decreased compared to participants without “recording,” regardless of the type of annotation, suggesting that subjects were cognizant of how they appear to other users in social reading apps.

If annotations by strangers is not as persuasive as those by computers or brands, as the first experiment showed, what about the effects of friend annotations? The second experiment examined the signed-in experience (with Googlers as subjects) and how they reacted to social annotations from friends, investigating whether personalized endorsements help people discover and select what might be more interesting content.

Perhaps not entirely surprising, results showed that friend annotations are persuasive and improve user satisfaction of news article selections. What’s interesting is that, in post-experiment interviews, we found that annotations influenced whether participants read articles primarily in three cases: first, when the annotator was above a threshold of social closeness; second, when the annotator had subject expertise related to the news article; and third, when the annotation provided additional context to the recommended article. This suggests that social context and personalized annotation work together to improve user experience overall.

Some questions for future research include whether or not highlighting expertise in annotations help, if the threshold for social proximity can be algorithmically determined, and if aggregating annotations (e.g. “110 people liked this”) help increases engagement. We look forward to further research that enable social recommenders to offer appropriate explanations for why users should pay attention, and reveal more nuances based on the presentation of annotations.

Read More..

Asus M70S First Notebook Capacity 1 Terabyte

During this development of the Notebook can be ascertained from the desktop is always left behind, both in the speed, the screen display to storage. However, it seems Asus is trying to prove that the notebook can offset the ability desktop. This is me with the Asus-series release its latest Notebook with a large. Asus notebook capacity issue in the size of the two series, namely Asus Asus M70S and M50S, they are the first notebook that have a capacity of 1 Terabyte (1 TB). The success of this Asus notebook both at once record this as a big notebook capacity (1 TB) is the first in the world.

From the design, not many changes made in the design of Asus Asus M70S. The notebook surface finishing high gloss plastic, giving the look elegant and clean. From the performance, the Asus M70S this brain processor Intel Core 2 Duo T9300 (2.5GHz, 6MB L2, 800MHz FSB) with a 4GB memory to guarantee speed, while support for the graphical display on the screen is 17 inch, Asus ATI M70S this practice Mobility Radeon HD 3650 with 1GB DDR2 video memory. 17 inch display screen of the Asus M70S is a notebook entry in this class of notebook PC, because the display screen of such an extent, of course this notebook is designed for the operating time is relatively long so that the user comfortable with the view that satisfactory.

The following are the technical specifications of the Asus M70S Notebook sold in the market with the price range $ 2399.99 U.S. dollars this:

1. Windows Vista Home Premium (32-bit)
2. Intel Core 2 Duo Processor T9300 (2.5GHz, 6MB L2, 800MHz FSB)
3. 17 "diagonal widescreen TFT LCD display at 1920x1200 (WUXGA, Glossy)
4. ATI Mobility Radeon HD 3650 with 1GB DDR2 video memory
5. Intel Wireless WiFi Link 4965AGN (802.11a/g/n)
6. 4GB PC2-5300 DDR2 SDRAM (maximum capacity 4GB)
7. 1TB Storage, 2 x 500GB Serial ATA hard disk drive (Hitachi 5400RPM)
8. DVD-Burner with 2x Blu-Ray reading capabilities
9. TV Tuner
10. 1.3 megapixel webcam
11. Fingerprint reader
12. Dimensions (WxDxH Front / H Rear): 16.2 "x 11.8" x 1.7 "
13. Weight: 8 lbs 13.1oz with nine-cell battery
14. 90W (19V x 4.74A) 100-240V AC Adapter
15. 9-cell (14.8V, 5200mAh) Lithium Ion battery
16. 2-Year Limited Global Warranty

What about you Asus M70S, First Notebook Capacity 1 Terabyte!

Read More..

3D Abstract High Definition HD Wallpapers for Computer

Read More..