Idiot Programmer

 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';

  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 - Insert records into table using PreparedStatement 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; public class Test { private static final String query = "insert into test1 values(?,?)"; public static void main(String[] args)throws Exception{ //generate connection try(Scanner scn = new Scanner(System.in)){ System.out.println("Enter The eid: "); int eid = scn.nextInt(); System.out.println("Enter employee name: "); String ename = scn.next(); try(Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); PreparedStatement ps = con.prepareStatement(query);){ ps.setInt(1, eid); ps.setString(2, ename); int count = ps.executeUpdate(); if(count==0) { System.o

How to get the row count in JDBC? For Explanation Watch Video:  Code:: package com.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.Scanner; public class Test { public static void main(String[] args)throws Exception{ //generate connection Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","tiger"); //sql Query String query = "select count(*) from stu"; //prparedStatement Object PreparedStatement ps = con.prepareStatement(query); //resultSet obj ResultSet rs = ps.executeQuery(); rs.next(); //fetch the data int count = rs.getInt(1); System.out.println("The No. Of Records : "+count); //close the connection con.close(); } }

 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

 Truncate Command (DDL) in SQL Oracle With Implementation For Explanation Watch Video:: Truncate:    -TO delete all rows from table at a time    - Deleting rows but not column    - By using this cmd we cant delete specific row from table    -It doesnt support where clause EX::: SQL> create table emp01(fname varchar2(10),sal Number(10)); Table created. SQL> insert into empo1 values('miller',500000); insert into empo1 values('miller',500000)             * ERROR at line 1: ORA-00942: table or view does not exist SQL> insert into emp01 values('miller',500000); 1 row created. SQL> insert into emp01 values('Baburao',50000); 1 row created. SQL> insert into emp01 values('Smith',500000); 1 row created. SQL> select * from emp01; FNAME             SAL ---------- ---------- miller         500000 Baburao         50000 Smith          500000 SQL> truncate table emp01; Table truncated. After Truncate:: SQL> select * from emp01; no rows se

 Primary Key::constraint ======================  ->It is combination of Unique and not null ->Restricted Duplicates and Nulls ->A table is having Only one primary Key At column Level:: ================= EX:: SQL> create table test2(eid int primary key,ename varchar2(10) primary key); create table test2(eid int primary key,ename varchar2(10) primary key)                                                           * ERROR at line 1: ORA-02260: table can have only one primary key SQL> create table test2(eid int primary key,ename varchar2(10)); Table created. SQL> insert into test2 values(1,'sam'); 1 row created. SQL> insert into test2 values(2,'dan'); 1 row created. SQL> select * from test2;        EID ENAME ---------- ----------          1 sam          2 dan SQL> insert into test2 values(null,'dan'); insert into test2 values(null,'dan')                          * ERROR at line 1: ORA-01400: cannot insert NULL into ("SYSTEM&quo

  Check Constraint::   ->to check values before accepting into a column with user defined condition at column level:: ================ >create table test1(eid int check(eid<5),ename varchar2(10)); EX:: SQL> create table test1(eid int check(eid<5),ename varchar2(10)); Table created. SQL> insert into test1 values(1,'dan'); 1 row created. SQL> insert into test1 values(2,'sam'); 1 row created. SQL> select * from test1;        EID ENAME ---------- ----------          1 dan          2 sam SQL> insert into test1 values(5,'sam'); insert into test1 values(5,'sam') * ERROR at line 1: ORA-02290: check constraint (SYSTEM.SYS_C007650) violated SQL> insert into test1 values(4,'sam'); 1 row created. SQL> select * from test1;        EID ENAME ---------- ----------          1 dan          2 sam          4 sam SQL> insert into test1 values(55,'sam'); insert into test1 values(55,'sam') * ERROR at line 1: ORA-02290

 Not Null Constraint: ====================   -Restricted nulls but accepting duplicates      -Not null an not be applied at table level ex:: create table test1(eid int not null,ename varchar2(10) not null); EX:: SQL> create table test1(eid int not null,ename varchar2(10) not null); Table created. SQL> insert into test1 values(10,'raja'); 1 row created. SQL> insert into test1 values(20,'dan'); 1 row created. SQL> select * from test1;        EID ENAME ---------- ----------         10 raja         20 dan SQL> insert into test1 values(null,'dan'); insert into test1 values(null,'dan')                          * ERROR at line 1: ORA-01400: cannot insert NULL into ("SYSTEM"."TEST1"."EID") SQL> insert into test1 values(10,null); insert into test1 values(10,null)                             * ERROR at line 1: ORA-01400: cannot insert NULL into ("SYSTEM"."TEST1"."ENAME") SQL> select *

Unique Constraint:: ================  -Restricted Duplicates values but accepting nulls into a column column level:: ============ >create table test1(sno int unique,name varchar2(10) unique); EX:: SQL> create table test1(eid int unique,ename varchar2(10) unique); Table created. SQL> insert into test1 values(10,'virat'); 1 row created. SQL> select * from test1;        EID ENAME ---------- ----------         10 virat SQL> insert into test1 values(10,'rohit'); insert into test1 values(10,'rohit') * ERROR at line 1: ORA-00001: unique constraint (SYSTEM.SYS_C007643) violated SQL> insert into test1 values(20,'virat'); insert into test1 values(20,'virat') * ERROR at line 1: ORA-00001: unique constraint (SYSTEM.SYS_C007644) violated SQL> insert into test1 values(20,'rohit'); 1 row created. SQL> select * from test1;        EID ENAME ---------- ----------         10 virat         20 rohit SQL> insert into test1(null,'

 Rename Command (DDL) in SQL Oracle With Implementation For explanation watch Video:: Rename command :: Used to change the name of table SQL> desc emp01;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  FNAME                                              VARCHAR2(15)  SAL                                                NUMBER(10) SQL> rename emp01 to emp02; Table renamed. SQL> desc emp01; ERROR: ORA-04043: object emp01 does not exist SQL> desc emp02;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  FNAME                                              VARCHAR2(15)  SAL                                                NUMBER(10)

 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               

 Create table in SQL For Explanation Watch Video:: EX:1: SQL> create table emp01(name varchar2(10),sal number(10)); Table created. How to see the Structure Of The table in SQL SQL> desc emp01;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  NAME                                               VARCHAR2(10)  SAL                                                NUMBER(10) EX2:: SQL> create table emp02(sno int,fname varchar2(10),lname varchar2(10),addr varchar2(10)); Table created. SQL> desc EMp02;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  SNO                                                 NUMBER(38)  FNAME                                           VARCHAR2(10)  LNAME                                           VARCHAR2(10)  ADDR                                              VARCHAR2(10)

 Revising Aggregations - The Sum Function Hackerrank Solution SQL For Explanation Watch Video : Code:: select sum(population) from city where district = 'California';

 Revising Aggregations - The Count Function Hackerrank Solution SQL For Explanation Watch Video : Code: select count(*) from city where population > 100000;

 Type of Triangle Hackerrank Solution SQL  For Explanation Watch Video : Code: SELECT CASE WHEN A+B<=C OR B+C<=A OR C+A<=B THEN 'Not A Triangle' WHEN A=B AND B=C THEN 'Equilateral' WHEN A=B OR B=C OR C=A THEN 'Isosceles' ELSE 'Scalene' END FROM TRIANGLES;

 Employee Salaries Hackerrank Solution SQL For Explanation Watch Video:  Code: select name from Employee where salary>2000 and months < 10 order by employee_id;

Employee Names Hackerrank Solution SQL   For Explanation Watch Video : Code: select name from Employee order by name;