Idiot Programmer

 183. Customers Who Never Order leetcode Solution For Explnation Watch video : Code:: select name Customers from Customers where id not in (select customerId  from Orders);

 African Cities Hackerrank Solution  Solution: select city.name from city inner join country on city.countrycode = country.code where country.CONTINENT = 'Africa';

 average population of each continent hackerrank solution oracle Code: select country.continent,floor(avg(city.population)) from city inner join country on city.countrycode = country.code group by country.continent;

 Interacting With Multiple Databases in Hibernate For Explanation watch video :: Directory Structure: pom.xml <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.test</groupId> <artifactId>OracleMySQLHB</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>OracleMySQLHB</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.9</maven.compiler.source> <maven.compiler.target>1.9</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>j

  Weather Observation Station 19 Hackerrank Solution - SQL  for explanation watch video :: Code:: select round(sqrt(     power(max(lat_n)-min(lat_N),2)+     power(max(long_w)-min(long_w),2) ),4) from station;

 Weather Observation Station 15 Hackerrank Solution - SQL For explanation watch video :: Code:: select round(long_w,4) from station where lat_n = (select max(lat_n) from station where lat_n<137.2345);

Weather Observation Station 14 Hackerrank Solution - SQL For explanation watch video :: Code:: select round(max(LAT_N),4) from station where LAT_N < 137.2345;

 Batch Processing in JDBC Code:: for explanation watch video : package com.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class OracleConn { public static void main(String[] args) { try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "tiger"); Statement st = con.createStatement()) { // add queries to batch ..these queries can belong to same db table or different // db tables but must be non-select Queries st.addBatch("INSERT INTO STUDENT VALUES(4444,'david',55.77,'france')"); st.addBatch("UPDATE  STUDENT SET AVG=AVG+10 WHERE SNO=2"); st.addBatch("DELETE FROM STUDENT WHERE SNO=1"); // execute the batch int result[] = st.executeBatch(); // find sum of the records that are effected int sum = 0; for (int i = 0; i < result.length; ++i)

  JDBC Auto Increment Primary Key in oracle Create table SQL> create table stu50      ( sid number(10) primary key,        sname varchar2(15),         sadd  varchar2(15)      ); Create a sequence. SQL> create sequence sid_seq      start with 1 increment by 1; JDBC code:: ========= import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class OracleConn { private static final String query = "INSERT INTO STU50 VALUES(SID_SEQ.NEXTVAL,?,?)"; public static void main(String[] args) { try { //Connection Obj Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); PreparedStatement ps = con.prepareStatement(query); ps.setString(1, "smith"); ps.setString(2, "pune"); int count = ps.executeUpdate(); if(count!=0) { System.out.println("Record

 Population Density Difference Hackerrank Solution - SQL For Explanation Watch Video : Code::  select max(population) - min(population) from city;

 Japan Population Hackerrank Solution - SQL For Explanation Watch Video :  Code:: select sum(population) from city where countrycode = 'JPN';

  JDBC - execute method in jdbc for select and non-select queries For Explanation Watch Video :: Creation of table:: SQL> create table test2(eid int,ename varchar2(10)); Table created. SQL> insert into test2 values(1,'vijay'); 1 row created. SQL> insert into test2 values(2,'rajesh'); 1 row created. SQL> select * from test2; EID ENAME ---------- ---------- 1 vijay 2 rajesh SQL> commit; Commit complete. JDBC code:: package com.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Scanner; class Test { public static void main(String arg[])throws Exception{ //generate the connection Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); //create statement Object Statement st = con.createStatement(); //get query from user Scanner

  The PADS Hackerrank Solution - SQL For Explanation Watch Video :  Oracle::  SELECT  NAME || '(' || SUBSTR(OCCUPATION,1,1) || ')' AS N FROM OCCUPATIONS ORDER BY N; SELECT 'There are a total of ' || COUNT(OCCUPATION) || ' ' || LOWER(OCCUPATION) || 's.' FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY COUNT(OCCUPATION), OCCUPATION; MySql: ======= SELECT CONCAT(NAME,'(',SUBSTR(OCCUPATION,1,1),')') AS NA FROM OCCUPATIONS ORDER BY NA; SELECT CONCAT('There are a total of ',COUNT(OCCUPATION),' ',LOWER(OCCUPATION),'s.') FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY COUNT(OCCUPATION), OCCUPATION;

 SQL Query to find total employees department wise For Explanation Watch Video:  SQL> select * from emp;      EMPNO ENAME      JOB               SAL     DEPTNO ----------      ----------      ----------      ----------           ----------       7788 scott             analyst          3630              10       7839 king             president        6050              10       7902 ford             analyst               3630            10       7903 miller           president        7000              20       7904 smith           professor        8000             20       7905 raja             clerk                 3630              30       7906 rajesh           salesman         8000             30       7907 raju             president             11000          30       7908 allu             clerk                 6050              20       7909 kyli             creater               5000             10       7910 james         clerk                 6000             20       1122 sa

JDBC Program For Age Calculator For Explanation Watch video :: Code :  import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Scanner; class Test   {  private static final String query = " select (sysdate-dob)/365.25 from emp88 where eid = ?";     public static void main(String arg[]){     try(Scanner scn = new Scanner(System.in)){     //take eid from user     System.out.println("Enter Emp id : ");     int eid = scn.nextInt();     try(Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger");     PreparedStatement ps = con.prepareStatement(query);){     //set the eid     ps.setInt(1, eid);     //ResultSet Object     ResultSet rs = ps.executeQuery();     rs.next();     //get the age     float age = rs.getFloat(1);     System.out.pr

 Drop Command (DDL) in SQL Oracle With Implementation For Explanation Watch Video:: Drop Command Used to delete  table (rows X columns) EX::: 1)Create table SQL> create table emp01(fname varchar2(15),sal Number(10)); Table created. 2)Describe table SQL> desc emp01;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  FNAME                                              VARCHAR2(15)  SAL                                                NUMBER(10) SQL> select * from emp01; no rows selected 3)Insert values into table SQL> insert into emp01 values('smith',500000); 1 row created. 4)Drop the table SQL> drop table emp01; Table dropped. 5)TO see columns deleted again describe table SQL> desc emp01; ERROR: ORA-04043: object emp01 does not exist 6_to see data deleted SQL> select * from emp01; select * from emp01               * ERROR at line 1: ORA-00942: table or view does not exist

 Creating table Using JDBC Application For Explanation Watch Video: EX1:: Code:: import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.*; public class Test1 { public static void main(String[] args)throws Exception{ Connection con =  DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); Statement st = con.createStatement(); //query String query = "create table test2(eid int,ename varchar2(10))"; //execute the query int count = st.executeUpdate(query); if(count==0){ System.out.println("Table is created"); }else{ System.out.println("Table is not created"); } con.close(); } } After Execution Of Program Table:: SQL> desc test2;  Name                                      Null?    Type  ----------------------------------------- -------- ------------

 JDBC application For Delete Operation Create Table:: SQL> create table test1(eid int,ename varchar2(10)); Table created. SQL> insert into test1 values(1,'vijay'); 1 row created. SQL> insert into test1 values(2,'rajesh'); 1 row created. SQL> select * from test1;        EID ENAME ---------- ----------          1 vijay          2 rajesh SQL> commit; Commit complete. JDBC application::: import java.sql.*; class Test { public static void main(String[] args)throws Exception{ //load JDBC driver //get connection Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); //create statement obj Statement st = con.createStatement(); //prepare and execute query String query = "delete from test1 where eid=2"; int count = st.executeUpdate(query); if(count==0){ System.out.println("No Row is deleted"); }else{ System.out.println(count+&qu

  JDBC Application For Insert Operation  For Explanation watch Video:: Create Table:: SQL>  create table test2(eid int,ename varchar2(10)); Table created. SQL> insert into test2 values(1,'vijay'); 1 row created. SQL> select * from test2;        EID ENAME ---------- ----------          1 vijay SQL> commit; Commit complete. Code:: import java.sql.*; class Test { public static void main(String[] args)throws Exception{ //load JDBC driver  Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); //create statement obj Statement st = con.createStatement(); //prepare and execute SQL query String query = "insert into test2 values(2,'rajesh')"; //execute the query int count = st.executeUpdate(query); if(count==0){ System.out.println("No row inserted"); }else{ System.out.println("row is inserted"); } con.close(); }

 ALTER Command (DDL) in SQL Oracle with Implementation on ORACLE For Explanation Watch video: 1)Alter Modify :: to modify column name or datatype EX:: SQL> create table emp01(name char(10),sal Number(10)); Table created. SQL> desc emp01;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  NAME                                               CHAR(10)  SAL                                                NUMBER(10) SQL> alter table emp01 modify name varchar2(10); Table altered. SQL> desc Emp01;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  NAME                                               VARCHAR2(10)  SAL                                                NUMBER(10) SQL> alter table emp01 modify name varchar2(15)/*to change the size of varchar2*/; Table altered. SQL> desc emp01;  Name