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) ...

 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    ...

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();     r...

 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 ...

 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(c...

  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        ...