SQL10 SQL Excercise, Requirement solution, Practice, Query Logic

Data in Table 

order_Day	order_id	prod_id	quantity	price
01-jul-2011	o1	p1	5	5
01-jul-2011	o2	p2	2	10
01-jul-2011	o3	p3	10	25
01-jul-2011	o4	p1	20	5
02-jul-2011	o5	p3	5	25
02-jul-2011	o6	p4	6	20
02-jul-2011	o7	p1	2	5
02-jul-2011	o8	p5	1	50
02-jul-2011	o9	p6	2	50
02-jul-2011	o10	p2	4	10

Output Needed

order_Day	prod_id	Sum
01-jul-2011	p3	250
02-jul-2011	p3	125

SELECT *
INTO SQL10
FROM (
	SELECT '01-jul-2011' order_Day
		,'o1' order_id
		,'p1' prod_id
		,5 quantity
		,5 price
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o2'
		,'p2'
		,2
		,10
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o3'
		,'p3'
		,10
		,25
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o4'
		,'p1'
		,20
		,5
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o5'
		,'p3'
		,5
		,25
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o6'
		,'p4'
		,6
		,20
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o7'
		,'p1'
		,2
		,5
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o8'
		,'p5'
		,1
		,50
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o9'
		,'p6'
		,2
		,50
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o10'
		,'p2'
		,4
		,10
	) t

Solution 1

SELECT *
FROM (
	SELECT ps.order_day
		,ps.prod_id
		,accum_sum
		,row_number() OVER (
			PARTITION BY ps.order_day ORDER BY accum_sum DESC
			) rn
	FROM SQL10 ps
	JOIN (
		SELECT quantity * price total_amount
			,order_day
			,order_id
			,sum(quantity * price) OVER (
				PARTITION BY order_day
				,prod_id ORDER BY order_day
					,prod_id
				) accum_sum
		FROM SQL10
		) ps1 ON ps.order_day = ps1.order_day
		AND ps.order_id = ps1.order_id
	) t
WHERE rn = 1

Solution 2

SELECT Order_Day

	,PrOd_id
	,SOLD_AMT
FROM (
	SELECT Order_Day
		,PrOd_id
		,SUM(quantity * Price) SOLD_AMT
		,DENSE_RANK() OVER (
			PARTITION BY Order_DaY ORDER BY SUM(quantity * Price) DESC
			) RNK
	FROM SQL10
	GROUP BY Order_Day
		,PrOd_id
	) t1
WHERE RNK = 1;

py0022 : 'python' is not recognized as an internal or external command,

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>python --version

'python' is not recognized as an internal or external command,

operable program or batch file.

Above error doesnt mean Python is not installed on your system. Python may or may not installed on your system. You can check this manually. Go to C drive search for python folder.

Default installation can be under path "C:\Users\******\AppData\Local\Programs\Python"

Foe me this command worked under path 

C:\Users\*****\AppData\Local\Programs\Python\Python37-32>python --version

Python 3.7.2

py0021 : Make Python connection with SQLServer database

Below is the code which can be used for to make connection with SQLServer database. Make sure python have already imported with pyodbc driver. If you have missing driver, use below command to install driver.

>pip install pyodbc

If you faced below message in output that means driver already installed
"Requirement already satisfied: pyodbc in c:\users\******\appdata\local\programs\python\python37-32\lib\site-packages (4.0.25)"

Code : 
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=IPAddress;'
                      'Database=databaseName;'
                      'username=sa;'
                      'password=dbpASSWORD;'
                      )
cursor = conn.cursor()
cursor.execute('SELECT * FROM information_schema.tables')
for row in cursor:
print(row)

py0020 : Python PIP install Error

I was trying to install pyodbc driver for sqlserver, but faced below error .

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>pip install pyodbc

Requirement already satisfied: pyodbc in c:\users\******\appdata\local\programs\python\python37-32\lib\site-packages (4.0.25)

You are using pip version 18.1, however version 20.3.3 is available.

You should consider upgrading via the 'python -m pip install --upgrade pip' command.

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>python -m pip install --upgrade pip

'python' is not recognized as an internal or external command,

operable program or batch file.

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>cd ..

C:\Users\******\AppData\Local\Programs\Python\Python37-32>python -m pip install --upgrade pip

Collecting pip

  Downloading https://files.pythonhosted.org/packages/54/eb/4a3642e971f404d69d4f6fa3885559d67562801b99d7592487f1ecc4e017/pip-20.3.3-py2.py3-none-any.whl (1.5MB)

    100% |████████████████████████████████| 1.5MB 1.5MB/s

Installing collected packages: pip

  Found existing installation: pip 18.1

    Uninstalling pip-18.1:

      Successfully uninstalled pip-18.1

Successfully installed pip-20.3.3

C:\Users\******\AppData\Local\Programs\Python\Python37-32>

Solution : You can upgrade PIP version, as command automatically suggested by the system.

It can be upgrade by using command "python -m pip install --upgrade pip", you might face error while installating this. Only need to take care we need to change the path on CMD where python.exe exists on file system

kyocera taskalfa 1800 "Add Toner" Error

• From Dial keypad, Enter Code, 10871087
• Press OK
• Dial/Enter 130 
• Press OK
• you can see on screen "No Action"
• Scroll dow using Arrow Key, until you will see "Execute" in place of "No Action"
• Press OK buttonm
• wait for upto 5 minutes (while execute you can see PLUS + sign will be on off on screen
• After completion of Execute, Press Reset or Stop button
• You can see "Maintenance" on screen
• Go to Option Print and Press "Print " Button
• machine will print the status of pages
• If above 4 step will not work shutdown machine and restart

How to Launch First Page using XAMPP on local machine

 How to Launch First Page using XAMPP on local machine

If you are new to XAMPP/PHP then this blog may help you. If you are not aware how to run php page on your local computer, read and follow below steps carefully

• Start XAMPP
• Make sure Apache/MySQL is running through XAMPP
• Go to installation folder of XAMPP
• Locate htdocs folder in installation directory "C:\xampp\htdocs\username"
• Create any Folder in "htdocs" folder
• Move in New folder created in "htdocs"
• create new file containing your PHP code
• Launch Chrome Browser or any other browser installed on your computer
• Enter URL "http://localhost/phpmyadmin/" to make sure XAMPP is up and running
• Enter url "http://localhost/username/first_.php" 

Note * - first_.php is the php page which is kept under htdocs folder, you must put your project under "htdocs" folder only

Create Search TextBox on Webpage

I have used this search textBox to search from the table, it can search any text (entered in text box) from the whole table and table will show only those rows where ever searched keyword found.

Once can modify the code as per their requirement.

Add below code in your header tab of html/xml code. Actual code is in "script" tag which is handling this implementation.

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
<script>
$(document).ready(function(){
  $("#myInput").on("keyup", function() {
    var value = $(this).val().toLowerCase();
    $("#ListAllPost tr").filter(function() {
      $(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
    });
  });
});
</script>
<style>
table {
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 100%;
}
td, th {
  border: 1px solid #dddddd;
  text-align: left;
  padding: 4px;
}
tr:nth-child(even) {
  background-color: #dddddd;
}
</style>
</head>
<body>
<h2>
Filter Posts</h2>
<input id="myInput" placeholder="Search.." type="text" />
<table><thead>
<tr>     <th style="width: 10%;">  Full Name  </th>     <th style="width: 45%;">Comment</th>     <th style="width: 45%;">Place</th>   </tr>
</thead>    <tbody id="ListAllPost">
<tr><td>Gurjeet Singh</td>     <td><a href="http://j4info.blogspot.com/p/blog-page_14.html">Enter any text in your text Box</td> <td>Click on the link to see the implementation</td> </tr>
<tr><td>J4Info</td>     <td><a href="http://j4info.blogspot.com/p/blog-page_14.html">Entered text will look from this table rows and accordingly show only that rows where ever searched keyword found.</a></td><td>Click on the link to see the implementation</td> </tr>
</tbody></table>
</body></html>
</div>

Use this code for

• To add search text button in your blog
• Search text in your website
• Add Search button in table
• Search keyword from tabular data