Sunday, March 26, 2023

UNIX SQL and Shell Scripting

  



What is the difference between job put on hold  and on ice?

on hold  - Depended jobs won’t run

on ice- dependent jobs will run

 

If the parent job is on hold, child job will run or not? No

 

If the parent  job is on Ice,child job will run or not? YES

 

Unix:

 

What is the difference between df and du

du reports files' and directories' disk usage, df reports how much disk space your filesystem is using.

 

Print sum of the file size using awk command 

$ ls -l *.* | awk '{ sum += $5 } END{ print sum }'     131446

What is the default permissions of file after created 

 the default value is 644 (rw-r--r--), meaning that the file's owner can read and write, and all others can only read this file

How do I fetch 2 lines before/after the grep result in bash?

 

Add prefix and suffix each line in file

awk '{ print "I am a", $0, "128... [}" }' file.txt

 

 

Some scenarios questions about how to troubleshoot when the file consuming high disk space without killing the process 

 

Ping command, netstat command 

Ping- help determine the connectivity to the remote host. ping 192.168.1.109

telnet 192.168.1.109 – connect remote host
Trying 192.168.1.109...

# netstat -a | more : To show both listening and 
non-listening sockets.

 

Find 60days older files and zip it

find . -mtime +60 -exec zip zipped.zip '{}' + && mv zipped.zip /var/ARCHIVES

 

Find the process details using ps command  ( ps –ef)

 

 

Sql:

Joins, group by 

Highest salary, 3rd highest salary, find duplicate records and delete duplicate records, self join to find nunber of employees working in each department

In Sql scenario questions like how to troubleshoot when query is taking more time than usual

 

  1. How to get the top second-highest CPU utilized process. (top)

top will display what is using your CPU. If you have it installed, htop allows you more fine-grained control, including filtering by—in your case—CPU

  1. How to kill all java processes or specific processes at a time with a single command.

kill -9 java

 

  1. How to know the server memory.
grep MemTotal /proc/meminfo

 

  1. How to reduce the size of a directory without modifying files or data.

$ tar cfz bigfile.tgz bigfile or $ gzip bigfile

5. How can you analyze the issue that needs code fix

6. what is indexing db. and types of index and max number of the index we can add on table and column.

technique that uses data structures to optimize the searching time of a database query.  you can have 32 Maximum Column Per Index

  • Unique indexes enforce the constraint of uniqueness in your index keys.
  • Bidirectional indexes allow for scans in both the forward and reverse directions.
  • Clustered indexes can help improve the performance of queries that traverse the table in key order.

7. Any automation you have done in the project and how you have done and need of it?

8. SQL daily run for 3 min but due to some issue the query running more than15 min why?

Data could be huge on that day,

Easy things to check - does the execution plan change?

Does the work done increase when you use certain bind variables?

 

9. SQL joins and subqueries

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

 

10. Find all .txt files in a directory and max depth with 3.

Find the passwd file under root and two levels down. (i.e root — level 1, and two sub-directories — level 2 and 3 )

find / -maxdepth 3 -name passwd

 

11. find the files which are accessible in the last 3 days.

find directory -iname "*.txt " -mtime  -n -print

find /home/priyanshu -iname "*.txt " -mtime  -3 –print

·       directory is the directory name in which the files are to be searched

·       -iname is the name of files and it could be a regex expression as well.

·       -mtime is to specify the number of days 

 

12. find the file and delete (find . -name "*.jpg" -exec /bin/rm -i {} \;)

13. the second-highest salary in a table. (select ename,sal from (select ename,sal from emp order by Sal DESC) where rownum=2;)

14. get the employee and its manager id if manager id does not exist update with no id keyword.

 

Please find below the inputs:

 

·       How To Run Awk Command Specified In A File? (sh my.awk.)

If you want to use awk -f my.awk life.csv > life_out.cs, then remove awk -F , ' and the last line from the file and add FS="," in BEGIN.

·       How To Get Only Zero Byte Files Which Are Present In The Directory?

find "$dir" -size 0 -print

·       list all the links from a directory?

ls -la /var/www/ | grep "\->" or

ls -lhaF | grep ^l   # list links

ls -lhaF | grep ^d   # list directories

ls -lhaF | grep ^-   # list files

·       which operating system your system is running on in UNIX - $ lsb_release -a

·       Difference between procedure and function

Functions always return

procedure can return a value using “IN OUT” and “OUT” arguments.

Each and every time functions are compiled they provide output according to the given input.

Procedures are compiled only once but they can be called many times as needed without being compiled each time.

The function can be called using Stored Procedure.

While procedures cannot be called from function.

A function used only to read data. A procedure can be used to read and modify data.

A function can be operated in the SELECT statement. Proc can’t be operated in the SELECT

 

·       what is trigger and hope it will work? Trigger is a smt , fires when DML smt triggers

special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

·       is commit will work on procedure ,function and trigger?

In a procedure definition, you can use COMMIT and ROLLBACK statements. But in a trigger body, you cannot use COMMIT and ROLLBACK statements. You can use only the WHENEVER SQLERROR ABORT statement.

·       how to check cpu utilization – using Top command

·       How do we see java process - $ ps ax |grep java

·       difference between delete (row wise delete data)and truncate (table retains)

·       what is join and types of join with an example

·       how to fetch/delete duplicate record in the database

delete from emp where max(rowid) not in ( select max(rowiD) from emp groupby ename)

·       how to delete duplicate record

·       how do we get 3rd highest sal from table give 2 different way how to extract that record

·       what is difference between case and decode - https://tipsfororacle.blogspot.com/2016/09/decode-vs-case.html

·       what is ITIL ,change and problem management.

https://www.manageengine.com/products/service-desk/itil/incident-problem-change-asset-management.html#:~:text=Change%20management&text=According%20to%20the%20ITIL%C2%AE,carried%20out%20with%20change%20control.

·       if any critical issue come how u will handle that issue

·       how you will handled job failure issue

https://stackoverflow.com/questions/51203215/autosys-job-fails

https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/autosys-workload-automation/12-0/troubleshooting/troubleshooting-ae/job-troubleshooting.html

 

·       do you handle releases how you will do it

https://asana.com/resources/release-management

https://www.lucidchart.com/blog/release-management-process

 

·       how to check process ( ps -aux)

·        how to kill process (kill -9 pid)

·        how to take tread dump

https://access.redhat.com/solutions/18178

https://www.ibm.com/docs/en/b2b-integrator/5.2?topic=troubleshooting-performing-thread-dump

·        use of awk command (manipulating data and generate reports- print the fourth field : awk '{ print $4 }' )

·       how to find a files which are more than 2GB (find /usr -type f -size +4G)

·       syntax for send mail in shell script($ echo "mail body" | mail -s "a subject" san@gmail.com)

·       how to find 3rd highest salary ( without using dence rank or rownum method)

·       how to create and use of index in db.

·        DB clusters

·       how to delete duplicate rows

·       knowledge about PlSql

·       zombie process is dead but have not been removed from the process table

·      

·       A kernel is responsible for accessing computer resources such as RAM and I/O devices. core component of any Linux based operating system

·       Counting occurrences of word in text file  - grep -wc "iPhone" san.txt

 

 

  

1.      Command to print all directories inside a directory - ls –d */

2.      One line command to print the sum of file sizes available in a directory

$ ls -l *.* | awk '{ sum += $5 } END{ print sum }'    

3.      Autosys questions like how we will get notification on job failure and how do you decode the issue from beginning

4.      If a job is unable to drop a file in particular location, what could have be the possible reasons?

5.      If a process is running long what you can do as a fix starting from finding the root cause

6.      More into technical side asking about my day to day activities and checking the technical knowledge by giving a scenario with question based on that

7.      2-3 question on Basi unix commands

8.      Shell scripting sample questions 3 nos

9.      Checking your analytical skill by giving a scenario in ITIL process and ow to debug

10.   Question for printing pattern pyramid and print fibonacci series unix

11.   SQl question for creating reports baked on the requirements

12.   Using string function tqo question on SQl

13.  All questions are related to performance issue in server and database. ( Trouble shooting)

14.  ITIL process – Problem Tickets

15.  One file is continuously generating log file, space is 98%, script should not stop.

a.      What  you will do in this situation.

https://www.cyberciti.biz/tips/shell-script-to-watch-the-disk-space.html

Shell script to watch the disk space and send an email

16.  Do you resolved any kind of performance error in Data base

17.  One script is running , need to place next to a file, but there is no space. why that script is not able to place that file,

18.   Application is down for a user, user cant excess that URL, what you will do.

19.  Aplication need to place to port 8080, which is already occupied, how you will trouble shoot.

20.   After resolving a issue what are the things you do.

21.   Inside a directory files are there, how you can check the total size of the file inside a directory.(One line Answer) - $ du -sh 1.2G

22.  What are the network commands you know.- ping, telnet, ssh

23.  In a table lots of rows and data are there, you need to delete all the data without using deleting command, how you will do it. Truncate or delete

24.  if Application crashed what you will do.

25.   Print Fibonacci series within the range of given number .

26.   Eg: If N is 15, it should print only 0 1 1 2 3 5 8 13

27.   Add prefix and suffix to each and every line of a file. – sed cmd

28.   Print only duplicate records from a table.

29.   What all things will you check when a user submitted form in UI is loading for a long time?

30.   What is release, change, incident and problem management?

31.   Tell about your scripts that you automated and general stuffs about the work and application.

32.   What is lock & deadlock and how it can be identified?

33.  you are unable to write files to a folder what could be the reason

Your issue is either a file system permission error or bad file path error.

34.  how will you check filesize of file in a directory

35.  print the sum of file sizes in a directory

36.  if job is writing huge log , how will you handle it without killing job

37.  there are multiple jobs failed at same time how ll you handle it .

38.  constraints in SQL -UNPFCD

39.  foreign key constraint

40.  if you want to write million of records one record we are unable to insert ,but you don't want the rest of it to fail . How ll you handle this

41.  scenario question on group by ... Dense rank

42.  Tell me about yourself and your roles and responsibilities

43.  Write a shell script to find the files in a directory which are older than 15 days and gzip them and move them to a different directory

find . -mtime +90 -exec zip zipped.zip '{}' + && mv zipped.zip /var/ARCHIVES

44.  How to print the lines which are not having the given patterns

awk '

    /pattern/ {

        # custom block to print the line   

    }

    !/pattern/ {

        # else do other things

    }

'

45.  How to identify the top memory using process

46.  Is there any possibility that memory usage is 120%?

47.   How to find the file sizes in a directory?

48.   More questions about application exceptions and user related queries

49.  Questions about indexes

50.  Complex questions on Group by statements

51.   And some general things like application related. 

52.  How would you check which process in unix consuming the memory ?

53.   What is the difference between delet, drop and truncate?

54.   What is clustered table and unclustered table?

55.   File which has 3 layer amd 3 colums , how u take rows on end.

56.  Difference b/n materialized view and process handling in  UNIX.

57.   2. how to find which directory taking long usages.

58.   3. How to check table space.

59.   4. How to kill process.

60.   5. How to concernate files in Unix.

61.   6. find highest incode usages.

62.   7. Data function in unix.

63.   8. Indexes and views in Linux.

64.   9. Indexes and views in SQL Database.

65.   10. Joins and DML statements in sql.

66.   11.if file 1 having name, marks and ids  and file 2 having id and status. Present file 1 may not available in file 2, how to get common records.

67.   12. If thousand java process is running  how to kill process named as java1, java 2, java 3.

68.   13. How to list file that are only with today date.

69.   14. What is view and why we will use it and when.

70.   15. During downtime how to kill all the process which are currently running.

71.   16. How to search for files by a specific date in file name.

72.   17. How to load data from oracle to another different oracle server. (DB Link)

73.   18. How to find Top 10 mount points which are highest consuming.

74.   19. How to get total count of a string in a file.

75.   20. How to Search for log files in specific directory which are more then 3years old removed then.

76.   21.ID names as S1, S2, S3,S4 in a table structure, how to find the ouput. 

77.   ID name all subjects marks, separated by  “ “   sum of subject and average of subjects.

78.   If average is less the 40%  false 0r true  should be populated.

79.   22. quaries are running fron long time, how do u apporch fixing issues.

80.   23.disc space

81.   24. java process running in unix , how do u list out running process which r using java code

82.   25.run the  sheel script in unix, it failed, how do u resolve

83.   26. how to replace Mth row Nth collomn with commadelimeter

84.   27. indexces.

85.   28.what type of automation u used, which has benefited for both the team and business

•            I have string like "string1' in the file every where i want to replace with 'string2' in the file every where how to do?

•            Difference b/w Grep and EGrep?

•            How do you check logs if Any failure happened how to you find process is running or not?

•            What is difference b/w Less and More command?

•            how do you go for end of file in Less command?

•            what is NVL and NVL2 function and difference?

•            How to get second highest salary?

•            what is view and how to create?

•            what is mean by trigger and how many types of triggers?

•            what is difference b/w Rank and Dencse_Rank ?

•            what are all cursor attributes?

•            If you dont see any error in the logfile what you do?

•            What type of joins using mostly in your project?

•            Can you tell me difference b/w inner join vs outer join ?

•            What is Dead Lock in Database any Dead Lock situation happened in your project table?

•            Have you done any perfomence tunning ?

•            Can you tell me which is better sub query or join why it is better?

•            If you are in primary host there are multiple other hosts where do you have files find other hosts and how to run the cmd for host2, host 3?

•            If you have file that file having lot of records how to import that file in Database what is the best approach?

•            If you have Database query that is long running you have to find which process from linux server is causing the query how to check?

•            If you have table one colum has primary key other columns has null values how do you find count of records does not have null values in any column?

 

No comments:

Post a Comment