Wednesday, October 11, 2023
Friday, September 22, 2023
Write a program that will read a list of student marks in the range 0 ... 100 and tell how many students scored in each range of 10 - how many scored 0 - 9, how many 10 -19, 20 - 29 ... and so on.
Input Format
-------------------
The first line of input consists of one positive integer which intimates the number of elements in the array.
Followed by the elements of the integer array.
Refer to the sample input for formatting specifications.
Output Format
----------------
The output consists of the number of elements present in the predefined ranges.
Refer to the sample output for formatting specifications.
Sample Input
7
23 45 76 103 87 -12 84
Sample Output
0-9: 0
10-19: 0
20-29: 1
30-39: 0
40-49: 1
50-59: 0
60-69: 0
70-79: 1
80-89: 2
90-100: 0
Invalid: 2
Sample Input
21
12 41 62 63 104 3 -9 72 55 73 98 107 -10 112 4 31 11 113 51 64 94
Sample Output
0-9: 2
10-19: 2
20-29: 0
30-39: 1
40-49: 1
50-59: 2
60-69: 3
70-79: 2
80-89: 0
90-100: 2
Invalid: 6
Given two strings, S and T, of equal length, pick some characters from the first string and some from the second string, and then form a new string by rearranging the characters picked. Find the length of the longest string that you can make, which will be a palindrome.
Example
Sample Input:
aba
bab
Sample Output:
5
Explanation:
One of the possible ways to form a palindrome of length 5 is by picking "aba" from the first string and "bab" from the second string, which gives "ababa".
Input Format
The first line contains a string S as input
The next line contains a string T as input.
Output Format
The output prints an integer, which denotes the maximum length of the palindrome that can be obtained.
Refer to the sample input and output for format specifications
Constraints
1<=|S| , |T|<=1000
Sample Input Sample Output
aba bab
Sample Output 5
Monday, August 21, 2023
20CS09-Object Oriented Programming
Object Oriented Programming Theory
Previous Sem Papers:
Text Books :
UNIT-I
UNIT-II
Object Oriented Programming lab
Thursday, July 13, 2023
Monday, June 5, 2023
Logic Building
Quiz Questions:
day 7 class (Variables, methods, static....)
day 8 Access Modifiers Inheritance & Polymorphism
day 9 :Packages, Interfaces & Exception Handling
Problem Solving:
CREATE TABLE SAILORS(SID NUMBER,SNAME VARCHAR2(10),RATING NUMBER(2),AGE NUMBER(4,2));
ALTER TABLE SAILORS ADD CONSTRAINT PK_SAILORS PRIMARY KEY(SID);
DESC SAILORS;
CREATE TABLE BOATS (BID NUMBER,BNAME VARCHAR2(10),COLOR VARCHAR2(10));
ALTER TABLE BOATS ADD CONSTRAINT PK_BOATS PRIMARY KEY(BID);
CREATE TABLE RESERVES(SID NUMBER,BID NUMBER,DAY DATE);
ALTER TABLE RESERVES ADD CONSTRAINT PK_RESERVES PRIMARY KEY(SID,BID,DAY);
ALTER TABLE RESERVES ADD CONSTRAINT FK_RESERVES FOREIGN KEY(SID) REFERENCES SAILORS(SID);
ALTER TABLE RESERVES ADD CONSTRAINT FK_RESERVES1 FOREIGN KEY(BID) REFERENCES BOATS(BID);
DESC RESERVES;
DESC BOATS;
INSERT INTO SAILORS VALUES(22,'DUSTIN',7,45.0);
INSERT INTO SAILORS VALUES(29,'BRUTUS',1,33.0);
INSERT INTO SAILORS VALUES(31,'LUBBER',8,55.5);
INSERT INTO SAILORS VALUES(32,'ANDY',8,25.5);
INSERT INTO SAILORS VALUES(58,'RUSTY',10,35.5);
INSERT INTO SAILORS VALUES(64,'HORATIO',7,35.0);
INSERT INTO SAILORS VALUES(71,'ZORBA',10,16.0);
INSERT INTO SAILORS VALUES(74,'HORATIO',9,35.0);
INSERT INTO SAILORS VALUES(85,'ART',3,25.0);
INSERT INTO SAILORS VALUES(95,'BOB',3,63.5);
COMMIT;
SELECT * FROM SAILORS;
INSERT INTO BOATS VALUES(101,'INTERLAKE','BLUE');
INSERT INTO BOATS VALUES(102,'INTERLAKE','RED');
INSERT INTO BOATS VALUES(103,'CLIPPER','GREEN');
INSERT INTO BOATS VALUES(104,'MARINE','RED');
SELECT * FROM BOATS;
INSERT INTO RESERVES VALUES(22,101,'10-OCT-1998');
INSERT INTO RESERVES VALUES(22,102,'10-OCT-1998');
INSERT INTO RESERVES VALUES(22,103,'10-AUG-1998');
INSERT INTO RESERVES VALUES(22,104,'10-JUL-1998');
INSERT INTO RESERVES VALUES(31,102,'11-OCT-1998');
INSERT INTO RESERVES VALUES(31,103,'11-JUN-1998');
INSERT INTO RESERVES VALUES(31,104,'11-DEC-1998');
INSERT INTO RESERVES VALUES(64,101,'09-MAY-1998');
INSERT INTO RESERVES VALUES(64,102,'09-AUG-1998');
INSERT INTO RESERVES VALUES(74,103,'09-AUG-1998');
SELECT * FROM RESERVES;
SQL Videos Link:
Wednesday, April 19, 2023
Tuesday, April 18, 2023
Monday, April 17, 2023
weather mining by taking weather data set using mapreduce
data sets :
https://www.ncei.noaa.gov/access/crn/qcdatasets.html
https://www.ncei.noaa.gov/pub/data/uscrn/products/monthly01/
weather data mining code
import java.io.IOException;
import java.util.Iterator;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;
import org.apache.hadoop.mapreduce.lib.input.TextInputFormat;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.Reducer;
import org.apache.hadoop.conf.Configuration;
public class MyMaxMin {
public static class MaxTemperatureMapper extends Mapper<LongWritable, Text, Text, Text>
{
public static final int MISSING=9999;
public void map(LongWritable arg0, Text Value, Context context) throws IOException, InterruptedException
{
String line = Value.toString();
if (!(line.length() == 0))
{
String date = line.substring(6, 14);
float temp_Min = Float.parseFloat(line.substring(39, 45).trim());
float temp_Max = Float.parseFloat(line.substring(47, 53).trim());
if (temp_Max > 35.0 && temp_Max!=MISSING)
{
context.write(new Text("Hot Day " + date),new Text(String.valueOf(temp_Max)));
}
if (temp_Min < 10 && temp_Max!=MISSING)
{
context.write(new Text("Cold Day " + date),new Text(String.valueOf(temp_Min)));
}
}
}
}
public static class MaxTemperatureReducer extends Reducer<Text, Text, Text, Text>
{
public void reduce(Text Key, Iterator<Text> Values, Context context)throws IOException, InterruptedException
{
String temperature = Values.next().toString();
context.write(Key, new Text(temperature));
}
}
public static void main(String[] args) throws Exception
{
Configuration conf = new Configuration();
Job job = new Job(conf, "weather example");
job.setJarByClass(MyMaxMin.class);
job.setMapOutputKeyClass(Text.class);
job.setMapOutputValueClass(Text.class);
job.setMapperClass(MaxTemperatureMapper.class);
job.setReducerClass(MaxTemperatureReducer.class);
job.setInputFormatClass(TextInputFormat.class);
job.setOutputFormatClass(TextOutputFormat.class);
Path OutputPath = new Path(args[1]);
FileInputFormat.addInputPath(job, new Path(args[0]));
FileOutputFormat.setOutputPath(job, new Path(args[1]));
System.exit(job.waitForCompletion(true) ? 0 : 1);
}
}
Matrix Multiplication with hadoop map reduce
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.conf.*;
import org.apache.hadoop.io.*;
import org.apache.hadoop.mapreduce.*;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.input.TextInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;
public class MatrixDriver
{
public static void main(String[] args) throws Exception
{
Configuration conf = new Configuration();
// M is an m-by-n matrix; N is an n-by-p matrix.
conf.set("m", "2");
conf.set("n", "2");
conf.set("p", "2");
Job job = Job.getInstance(conf, "MatrixMultiplication");
job.setJarByClass(MatrixDriver.class);
job.setOutputKeyClass(Text.class);
job.setOutputValueClass(Text.class);
job.setMapperClass(MatrixMapper.class);
job.setReducerClass(MatrixReducer.class);
job.setInputFormatClass(TextInputFormat.class);
job.setOutputFormatClass(TextOutputFormat.class);
FileInputFormat.addInputPath(job, new Path(args[0]));
FileOutputFormat.setOutputPath(job, new Path(args[1]));
job.submit();
}
}
import java.io.IOException;
import java.util.*;
import org.apache.hadoop.io.*;
import org.apache.hadoop.mapreduce.*;
public class MatrixReducer extends Reducer<Text, Text, Text, Text>
{
public void reduce(Text key, Iterable<Text> values, Context context) throws IOException, InterruptedException
{
String[] value;
HashMap<Integer, Float> hashA = new HashMap<Integer, Float>();
HashMap<Integer, Float> hashB = new HashMap<Integer, Float>();
for (Text val : values)
{
value = val.toString().split(",");
if (value[0].equals("M"))
{
hashA.put(Integer.parseInt(value[1]), Float.parseFloat(value[2]));
}
else
{
hashB.put(Integer.parseInt(value[1]), Float.parseFloat(value[2]));
}
}
int n = Integer.parseInt(context.getConfiguration().get("n")); float result = 0.0f;
float a_ij; float b_jk;
for (int j = 0; j < n; j++)
{
a_ij = hashA.containsKey(j) ? hashA.get(j) : 0.0f;
b_jk = hashB.containsKey(j) ? hashB.get(j) : 0.0f;
result += a_ij * b_jk;
}
if (result != 0.0f)
{
context.write(null, new Text(key.toString() + "," + Float.toString(result)));
}
}
}
import java.io.IOException;
import org.apache.hadoop.conf.*;
import org.apache.hadoop.io.*;
import org.apache.hadoop.mapreduce.*;
public class MatrixMapper extends Mapper<LongWritable, Text, Text, Text>
{
public void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException
{
Configuration conf = context.getConfiguration();
int m = Integer.parseInt(conf.get("m"));
int p = Integer.parseInt(conf.get("p"));
String line = value.toString();
String[] indicesAndValue = line.split(",");
Text outputKey = new Text();
Text outputValue = new Text();
if (indicesAndValue[0].equals("M"))
{
for (int k = 0; k < p; k++)
{
outputKey.set(indicesAndValue[1] + "," + k);
outputValue.set("M," + indicesAndValue[2] + "," + indicesAndValue[3]);
context.write(outputKey, outputValue);
}
}
else
{
for (int i = 0; i < m; i++)
{
outputKey.set(i + "," + indicesAndValue[2]);
outputValue.set("N," + indicesAndValue[1] + "," + indicesAndValue[3]); context.write(outputKey, outputValue);
}
}
}
}
source code files
Friday, April 14, 2023
Monday, April 10, 2023
Sunday, April 9, 2023
Hadoop MapReduce word count code
import java.io.IOException;
import java.util.StringTokenizer;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.Reducer;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
public class WordCount {
public static class TokenizerMapper
extends Mapper<Object, Text, Text, IntWritable>{
private final static IntWritable one = new IntWritable(1);
private Text word = new Text();
public void map(Object key, Text value, Context context
) throws IOException, InterruptedException {
StringTokenizer itr = new StringTokenizer(value.toString());
while (itr.hasMoreTokens()) {
word.set(itr.nextToken());
context.write(word, one);
}
}
}
public static class IntSumReducer
extends Reducer<Text,IntWritable,Text,IntWritable> {
private IntWritable result = new IntWritable();
public void reduce(Text key, Iterable<IntWritable> values,
Context context
) throws IOException, InterruptedException {
int sum = 0;
for (IntWritable val : values) {
sum += val.get();
}
result.set(sum);
context.write(key, result);
}
}
public static void main(String[] args) throws Exception {
Configuration conf = new Configuration();
Job job = Job.getInstance(conf, "word count");
job.setJarByClass(WordCount.class);
job.setMapperClass(TokenizerMapper.class);
job.setCombinerClass(IntSumReducer.class);
job.setReducerClass(IntSumReducer.class);
job.setOutputKeyClass(Text.class);
job.setOutputValueClass(IntWritable.class);
FileInputFormat.addInputPath(job, new Path(args[0]));
FileOutputFormat.setOutputPath(job, new Path(args[1]));
System.exit(job.waitForCompletion(true) ? 0 : 1);
}
}
Tuesday, January 31, 2023
Thursday, January 26, 2023
Monday, January 9, 2023
BDA LINK
https://t.me/+3KfpUyNr6oI5Mjdl
Great Places to Find Free Datasets for Project
1. Google Dataset Search
https://datasetsearch.research.google.com/
2. Kaggle
https://www.kaggle.com/datasets
3. Data.Gov
https://data.gov/
https://indiaai.gov.in/datasets
4. Datahub.io
https://datahub.io/collections
5. UCI Machine Learning Repository
https://archive.ics.uci.edu/ml/datasets.php
6. Earth Data
https://www.earthdata.nasa.gov/
7. CERN Open Data Portal
http://opendata.cern.ch/
https://data.world/datasets/india
8. Global Health Observatory Data Repository
https://apps.who.int/gho/data/node.home
9. FBI Crime Data Explorer
https://cde.ucr.cjis.gov/LATEST/webapp/#/pages/home
10.NYC Taxi Trip Data
https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page
Friday, December 30, 2022
Tuesday, December 20, 2022
Tuesday, December 13, 2022
Employee All Answers
1.Retrieve the birth date and address of the employee(s) whose name is 'John B.Smith'.
SQL> select bdate,address from employee where fname='john' and minit='B' and lname='smith';
BDATE ADDRESS
--------- ----------------------------------------
09-JAN-65 731 Foundren,houstan,Tx
2.Retrieve the name and address of all employees who work for the 'Research' department.
SQL> select fname,minit,lname,address from employee where dno=(select dnum from department where dname='research');
FNAME M LNAME ADDRESS
-------------------- - -------------------- ----------------------------------------
john B smith 731 Foundren,houstan,Tx
franklin T wong 638,Voss,Houstan,Tx
ramesh k narayan 975 Fire Oak,Humble,TX
Joyce A English 5631 Rice,Houstan,Tx
3.For every project located in 'Stafford',list the project number,the controlling department number,and the department manager's last name,address,and birth date.
SQL> select p.pnum,p.dnum,e.bdate,e.address from employee e,department d,project p Where p.dnum=d.dnum and d.dnum=e.dno and p.plocations='Stafford' and d.mgr_ssn=e.ssn;
PNUM DNUM BDATE ADDRESS
---------- ---------- --------- ----------------------------------------
30 4 20-JUN-41 291Berry,Bellaire,Tx
10 4 20-JUN-41 291Berry,Bellaire,Tx
4.Retrieve the names of all employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
SQL> select e.fname,e.minit,e.lname from employee e,works_on w,project p where e.ssn=w.essn and w.pno=p.pnum and p.pname='ProductX' and w.hours>=10.0 and e.dno=5;
FNAME M LNAME
-------------------- - --------------------
john B smith
Joyce A English
5.List the names of all employees who have a dependent with the same first name as themselves.
SQL> select e.fname,e.minit,e.lname from employee e,dependent d where e.ssn=d.essn and e.fname=d.dependent_name;
no rows selected
6.Find the names of all employees who are directly supervised by 'Franklin Wong'.
SQL> select e.fname,e.minit,e.lname from employee e Where e.super_ssn=(select ssn from employee e2 where e2.fname='franklin' and e2.lname='wong');
FNAME M LNAME
-------------------- - --------------------
john B smith
franklin T wong
ramesh k narayan
Joyce A English
7.Make a list of all project numbers for project that involve an employee whose last name is 'Smith',either as a worker or as a manager of the department that controls the project.
SQL> (select w.pno from works_on w,employee e where w.essn=e.ssn and e.lname='smith') union (select pnum from employee e,department d,project p where e.ssn=d.mgr_ssn and e.lname='smith' and p.dnum=d.dnum);
PNO
----------
1
2
8.for each employee ,retrive the employee's first and last name and the first and last name of his or her immediate supervisor.
SQL> select e.fname,e.lname,s.fname,s.lname from employee e,employee s where e.super_ssn=s.ssn;
FNAME LNAME FNAME LNAME
-------------------- -------------------- -------------------- --------------------
Joyce English franklin wong
ramesh narayan franklin wong
franklin wong franklin wong
john smith franklin wong
alicia Zelaya alicia Zelaya
Ahmad jabbar Ahmad jabbar
jennifer wallace james brog
7 rows selected.
9.select all employees ssn and all combinations of employees ssn and department dname in the database.
10.Retrieve the salary of every employee and all distinct salary values.
SQL> select distinct(salary) from employee;
SALARY
----------
38000
43000
55000
30000
40000
25000
6 rows selected.
11.Retrieve all employees whose address is in Houston, Texas.
SQL> select address from employee where address like '%Houstan,Tx';
ADDRESS
----------------------------------------
638,Voss,Houstan,Tx
5631 Rice,Houstan,Tx
980 Dallas,Houstan,Tx
450 Stone,Houstan,Tx
731 Foundren,houstan,Tx
12.Find all employees who were born during the 1950s.
SQL> select fname,minit,lname,bdate from employee where bdate like '_______5_';
FNAME M LNAME BDATE
-------------------- - -------------------- ---------
franklin T wong 08-DEC-55
SQL> select fname,minit,lname,bdate from employee where to_char(bdate,'YYYY')=1955;
FNAME M LNAME BDATE
-------------------- - -------------------- ---------
franklin T wong 08-DEC-55
13.Show the resulting salaries if every employee working on the 'ProductX' project is given a 10 percent raise.
SQL> select salary+(salary*10)/100 from employee e,works_on w,project p Where e.ssn=w.essn and w.pno=p.pnum and pname='ProductX';
SALARY+(SALARY*10)/100
----------------------
33000
27500
14.Retrieve all employees in department 5 whose salary is between 30000 and 40000.
SQL> select fname,minit,lname,dno,salary from employee Where dno=5 and salary between 30000 and 40000;
FNAME M LNAME DNO SALARY
-------------------- - -------------------- ---------- ----------
john B smith 5 30000
franklin T wong 5 40000
ramesh k narayan 5 38000
15.Retrieve a list of employees and the projects they are working on, ordered by department and,within each department, ordered alphabetically by last name, then first name.
SQL> select e.fname,p.pname from employee e,works_on w,project p where e.ssn=w.essn and w.pno=p.pnum order by fname asc;
FNAME PNAME
-------------------- ---------------
Ahmad Newbenefits
Ahmad Computerization
Joyce ProductY
Joyce ProductX
alicia Computerization
alicia Newbenefits
franklin ProductZ
franklin ProductY
franklin Computerization
franklin Reorganization
james Reorganization
FNAME PNAME
-------------------- ---------------
jennifer Newbenefits
jennifer Reorganization
john ProductX
john ProductY
ramesh ProductZ
16 rows selected.
16.Find the names of employees who work on all the projects controlled by department number 5.
17.List the names of all employees with two or more dependents.
SQL> select fname,minit,lname from employee where ssn in(select essn from dependent group by essn having count(essn)>=2);
FNAME M LNAME
-------------------- - --------------------
franklin T wong
john B smith
18.Retrieve the names of employees who have no dependents.
SQL> select fname,minit,lname from employee where ssn not in(select distinct essn from dependent);
FNAME M LNAME
-------------------- - --------------------
Ahmad V jabbar
james e brog
Joyce A English
alicia J Zelaya
ramesh k narayan
19.List the names of managers who have atleast one dependent.
SQL> select fname,minit,lname from employee e,department d where e.ssn=d.mgr_ssn and ssn in(select essn from dependent group by essn having count(essn)>=1);
FNAME M LNAME
-------------------- - --------------------
franklin T wong
jennifer S wallace
20.Retrieve the names of all employees who donot have supervisers.
SQL> select fname,minit,lname from employee where super_ssn is null;
FNAME M LNAME
-------------------- - --------------------
james e brog
21.Retrieve the name of each employees who has a dependent with the same first name and is the same sex as the employee.
SQL> select e.fname,e.minit,e.lname from employee e,dependent d Where e.ssn=d.essn and e.fname=d.dependent_name and e.sex=d.sex;
no rows selected
22.Retrieve the social security numbers of all employees who work on project number 1,2, or 3.
SQL> select e.ssn from employee e,project p where p.dnum=e.dno and p.pnum in(1,2,3);
SSN
----------
123456789
123456789
123456789
333445555
333445555
333445555
666884444
666884444
666884444
453453453
453453453
SSN
----------
453453453
23.Find the sum of salaries of all employees,the maximum salary,the minimum salary,and the average salary.
SQL> select sum(salary),avg(salary),max(salary),min(salary) from employee;
SUM(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY)
----------- ----------- ----------- -----------
281000 35125 55000 25000
24.Find the sum of the salaries of all employees of the 'Research' department,as well as the maximum salary,the minimum salary,and the average salary in this department.
SQL> select sum(salary),avg(salary),max(salary),min(salary) from employee e,department d where e.dno=d.dnum and d.dname='research';
SUM(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY)
----------- ----------- ----------- -----------
133000 33250 40000 25000
25.Retrieve the total number of employees in the company.
SQL> select count(ssn) from employee;
COUNT(SSN)
----------
8
26.Retrieve the number of employees in the 'Research' department.
SQL> select count(ssn) from employee e,department d where e.dno=d.dnum and d.dname='research';
COUNT(SSN)
----------
4
27.Count the number of distinct salary values in the database.
SQL> select count(distinct(salary)) from employee;
COUNT(DISTINCT(SALARY))
-----------------------
6
28.For each department, retrieve the department number, the number of employees in the department and their average salary.
SQL> select avg(salary),dno,count(ssn) from employee group by dno;
AVG(SALARY) DNO COUNT(SSN)
----------- ---------- ----------
55000 1 1
33250 5 4
31000 4 3
29.For each project, retrieve the project number, the project name, and the number of employees who work on that project.
SQL> select p.pnum,p.pname,count(e.ssn) from employee e,project p,works_on w where w.pno=p.pnum and e.ssn=w.essn group by pnum,pname;
PNUM PNAME COUNT(E.SSN)
---------- --------------- ------------
20 Reorganization 3
1 ProductX 2
10 Computerization 3
30 Newbenefits 3
2 ProductY 3
3 ProductZ 2
6 rows selected.
30.For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.
SQL> select p.pnum,p.pname,count(e.ssn) from employee e,works_on w,project p where p.pnum=w.pno and e.ssn=w.essn group by p.pnum,p.pname having count(e.ssn)>2;
PNUM PNAME COUNT(E.SSN)
---------- --------------- ------------
20 Reorganization 3
10 Computerization 3
30 Newbenefits 3
2 ProductY 3
31.For each project, retrieve the project number,the project name, and the number of employees from department 5 who work on the project.
SQL> select p.pnum,p.pname,count(e.ssn) from employee e,project p,works_on w Where e.ssn=w.essn and p.pnum=w.pno and e.dno=5 group by p.pnum,p.pname;
PNUM PNAME COUNT(E.SSN)
---------- --------------- ------------
20 Reorganization 1
1 ProductX 2
10 Computerization 1
2 ProductY 3
3 ProductZ 2
32.For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than 40000.
SQL> select e.dno,count(e.ssn) from employee e,department d where e.salary>40000 group by e.dno having count(e.ssn)>=3;
DNO COUNT(E.SSN)
---------- ------------
1 3
4 3
33.DISPLAY ALL EMPLOYEE NAMES ALONG WITH THEIR DEPARTMENT NAMES.
SQL> select e.fname,e.mi,e.lname,d.dname from employee e,department d where e.dno=d.dnum;
FNAME M LNAME DNAME
--------------- - --------------- --------------------
John B Smith Research
Franklin T Wong Research
Alicia J Zelaya Administartion
Jennifer S Wallace Administartion
Ramesh K Narayana Research
Joyce A English Research
Ahmad V Jabbar Administartion
James E Brog Headquarters
8 rows selected.
34. DISPLAY ALL THE EMPLOYEE NAMES ALONG WITH DEPENDENT NAMES.
SQL> select e.fname,e.mi,e.lname,dependent_name from employee e,dependent d where e.ssn=d.essn;
FNAME M LNAME DEPENDENT_NAME
--------------- - --------------- --------------------
John B Smith Elizabeth
John B Smith Michael
Franklin T Wong Alice
Franklin T Wong Joy
Franklin T Wong Theodore
Jennifer S Wallace Abner
6 rows selected.
35.LIST THE NAMES OF EMPLOYEES ALONG WITH THE NAMES OF THEIR SUPERVISOR USING ALIAS.
SQL> select e.fname,e.lname,e.mi,s.fname,s.lname,s.mi from employee e,employee s where e.superssn=s.ssn;
FNAME LNAME M FNAME LNAME M
--------------- --------------- - --------------- --------------- -
John Smith B Franklin Wong T
Franklin Wong T James Brog E
Alicia Zelaya J Jennifer Wallace S
Jennifer Wallace S James Brog E
Ramesh Narayana K Franklin Wong T
Joyce English A Franklin Wong T
Ahmad Jabbar V Ahmad Jabbar V
7 rows selected.
36.DISPLAY NAMES OF THE DEPARTMENT AND NAME OF MANAGER FOR ALL DEPARTMENTS
SQL> select d.dname,e.fname,e.lname,e.mi from department d, employee e where e.ssn=d.mgrssn;
DNAME FNAME LNAME M
-------------------- --------------- --------------- -
Research Franklin Wong T
Administartion Jennifer Wallace S
Headquarters James Brog E
37.LIST THE DEPARTMENT OF EACH FEMALE EMPLOYEE ALONG WITH HER NAME.
SQL> select e.fname,e.mi,e.lname,d.dname from employee e,department d where e.sex='F' and e.dno=d.dnum;
FNAME M LNAME DNAME
--------------- - --------------- --------------------
Alicia J Zelaya Administartion
Jennifer S Wallace Administartion
Joyce A English Research
38.LIST ALL EMPLOYEE NAMES AND ALSO THE NAMES OF THE DEPARTMENT THEY MANAGE IF THEY HAPPEN TO MANAGE A DEPT.
SQL> select d.dname,e.fname,e.lname,e.mi from employee e,department d where e.ssn=d.mgrssn;
DNAME FNAME LNAME M
-------------------- --------------- --------------- -
Research Franklin Wong T
Administartion Jennifer Wallace S
Headquarters James Brog E
39.RETRIEVE THE NAMES OF EMPLOYEE WHO WORK ON ALL THE PROJECTS THAT 'JOHN' WORK ON
SQL> select e.fname,e.lname,e.mi from employee e where ssn in(select essn from works_on where pno in(select pno from works_on w,employee e,project p where e.ssn=w.essn and e.fname='James'));
FNAME LNAME M
--------------- --------------- -
Franklin Wong T
Jennifer Wallace S
James Brog E
40.FOR EACH PROJECT,LIST THE PROJECT NAME AND TOTAL HOURS(BY ALL EMPLOYEE) SPENT ON THAT PROJECT.
SQL> select pname,sum(hours) from project,works_on where project.pnum=works_on.pno group by pname;
PNAME SUM(HOURS)
-------------------- ----------
Computerization 55
ProductZ 50
ProductY 37.5
ProductX 52.5
Reorganization 25
Newbenifits 55
6 rows selected.
41.DISPLAY THE NAMES AND TOTAL NUMBER OF HOURS WORKED BY AN EMPLOYEE WHO IS WORKING ON MAXIMUM NUMBER OF PROJECTS AMONG ALL THE EMPLOYEE.
SQL> select fname,mi,lname,sum(hours) from employee e,works_on w where essn in(select essn from works_on group by essn having count(essn)=(select max(count(essn)) from works_on group by essn)) and e.ssn=w.essn group by fname,lname,mi;
FNAME M LNAME SUM(HOURS)
--------------- - --------------- ----------
Franklin T Wong 40
42.DISPLAY THE NAMES OF ALL EMPLOYEES AND ALSO NO OF HOURS,PROJECT NAMES THAT THEY WORK ON ON THEY HAPPEN TO WORK ON ANY PROJECT(USE OUTER JOIN).
SQL> create view dbms as(select * from employee e,works_on w where e.ssn=w.essn);
View created.
SQL> select fname,hours,pname from dbms full outer join project on dbms.pno=project.pnum;
FNAME HOURS PNAME
--------------- ---------- --------------------
John 32.5 ProductX
John 7.5 ProductY
Ramesh 40 ProductZ
Joyce 20 ProductX
Joyce 20 ProductY
Franklin 10 ProductY
Franklin 10 ProductZ
Franklin 10 Computerization
Franklin 10 Reorganization
Alicia 30 Newbenifits
Alicia 10 Computerization
FNAME HOURS PNAME
--------------- ---------- --------------------
Ahmad 35 Computerization
Ahmad 5 Newbenifits
Jennifer 20 Newbenifits
Jennifer 15 Reorganization
James Reorganization
16 rows selected.
43.LIST THE EMPLOYEE NAME,PROJECT NAME ON WHICH THEY WORK AND THE DERPARTMENT THEY BELONG TO FOR ALL THE EMPLOYEES USING ALIAS NAMES FOR THE RESULTING COLUMNS.
SQL> select fname,lname,mi,dname,pname from employee e,project p,department d,works_on w where w.pno=p.pnum and w.essn=e.ssn and d.dnum=e.dno;
FNAME LNAME M DNAME PNAME
--------------- --------------- - -------------------- --------------------
John Smith B Research ProductX
John Smith B Research ProductY
Franklin Wong T Research ProductY
Franklin Wong T Research ProductZ
Franklin Wong T Research Computerization
Franklin Wong T Research Reorganization
Joyce English A Research ProductX
Joyce English A Research ProductY
Ramesh Narayana K Research ProductZ
James Brog E Headquarters Reorganization
Jennifer Wallace S Administartion Reorganization
FNAME LNAME M DNAME PNAME
--------------- --------------- - -------------------- --------------------
Jennifer Wallace S Administartion Newbenifits
Ahmad Jabbar V Administartion Computerization
Ahmad Jabbar V Administartion Newbenifits
Alicia Zelaya J Administartion Computerization
Alicia Zelaya J Administartion Newbenifits
16 rows selected.
44.LIST ALL THE DEPARTMENTS THAT CONTAIN AT LEAST ONE OCCURENCE OF C IN THEIR NAMES.
SQL> select dname from department where dname like '%c%';
DNAME
--------------------
Research
45.LIST THE PROJECTS THAT ARE CONTROLLED BY ONE DEPARTMENT.
SQL> select pname from project p,department d where p.dnum=d.dnum and d.dnum=1;
PNAME
--------------------
Reorganization
46.LIST THE MANAGERS OF THE CONTROLLING DEPARTMENT FOR ALL THE PROJECTS.
SQL> select d.dname,p.pname,e.fname from employee e,project p,department d where d.dnum=p.dnum and e.dno=d.dnum and d.mgrssn=e.ssn;
DNAME PNAME FNAME
-------------------- -------------------- ---------------
Research ProductX Franklin
Research ProductY Franklin
Research ProductZ Franklin
Administartion Computerization Jennifer
Headquarters Reorganization James
Administartion Newbenifits Jennifer
6 rows selected.
47.LIST ALL THE LOCATION OF THE CONTROLLING DEPARTMENT FOR ALL THE PROJECTS.
SQL> select d.dlocation,p.pname,ds.dname from project p,dept_locations d,department ds where d.dnum=p.dnum and ds.dnum=p.dnum;
DLOCATION PNAME DNAME
---------- -------------------- --------------------
Bellaire ProductX Research
Houstan ProductX Research
Sugarland ProductX Research
Bellaire ProductY Research
Houstan ProductY Research
Sugarland ProductY Research
Bellaire ProductZ Research
Houstan ProductZ Research
Sugarland ProductZ Research
Stafford Computerization Administartion
Houstan Reorganization Headquarters
DLOCATION PNAME DNAME
---------- -------------------- --------------------
Stafford Newbenifits Administartion
12 rows selected.
SQL>
Subscribe to:
Posts (Atom)
-
https://meet.google.com/qjv-erbu-ewf Lab Syllabus Theory Syllabus WEEK1 WEEK2 ----> Insertion WEEK3 WEEK4 WEEK5 WEEK6 -----> Solutions...
-
Lucky Gifts "Planet Kids Entertainment Fair" is back to delight kids and parents. The Fair will have non-stop entertainment w...
-
Classroom link: https://classroom.google.com/c/ODc0OTQxMjY1MjA5?cjc=rq67ikqo class code: rq67ikqo
